sqlserver调优
来源:互联网 发布:教育统计软件安装 编辑:程序博客网 时间:2024/06/07 07:16
1.索引使用
sqlserver聚集索引是真正加快查询速度的东西,而且索引在查询中能否优化是看你建立的索引是否是在你查询的条件或者group by语句中,而不是你的select查询出来的结果字段上。
2.union all/union
select sum(a.AddPnt1) as num,a.ChrId1 FROM( select ChrId1,AddPnt1 from W_GldMatchBalanceLog where LogTime BETWEEN '2017-08-14' and '2017-08-15'UNION ALL select ChrId2,AddPnt2 from W_GldMatchBalanceLog where LogTime BETWEEN '2017-08-14' and '2017-08-15'UNION ALL select ChrId3,AddPnt3 from W_GldMatchBalanceLog where LogTime BETWEEN '2017-08-14' and '2017-08-15'UNION ALL select ChrId4,AddPnt4 from W_GldMatchBalanceLog where LogTime BETWEEN '2017-08-14' and '2017-08-15') as a where a.ChrId1 <> 0 GROUP BY ChrId1 ORDER BY num DESC
当你需要取同一个查询结果中的多个值时你可能需要多长查询然后再union而sql2005以后出现了相当于面向对象语言里面的变量的概念的关键字with as能够把查询的结果存起来
select sum(a.AddPnt1) as num,a.ChrId1 FROM(with cr AS(SELECT ChrId1,AddPnt1,ChrId2,AddPnt2,ChrId3,AddPnt3,ChrId4,AddPnt4 from W_GldMatchBalanceLog where LogTime BETWEEN '2017-08-14' and '2017-08-15')SELECT ChrId1,AddPnt1 FROM crUNION ALLSELECT ChrId2,AddPnt2 FROM crUNION ALLSELECT ChrId3,AddPnt3 FROM crUNION ALLSELECT ChrId4,AddPnt4 FROM cr)
下面代码的效率是上面的一倍多,而且数据量越大差距越明显
阅读全文
0 0
- sqlserver调优
- sqlserver
- SQLServer
- sqlserver
- sqlserver
- SqlServer
- sqlserver
- sqlserver
- sqlserver
- SQLServer
- SQLServer
- sqlserver
- sqlserver
- SqlServer
- sqlserver
- SQLServer
- SqlServer
- SqlServer
- HDU 1879 继续畅通工程
- UML类图(PHP)
- Spring学习笔记
- java调用浏览器执行搜索
- python中的实例方法、类方法、静态方法
- sqlserver调优
- 二叉排序树、平衡二叉树、B-树
- socket编程为什么要选择AF_INET
- Java中的枚举、接口和代码块
- 【个人训练】(HDU2199)Can you solve this equation?
- 阿里巴巴CTO行癫:阿里双11是世界互联网技术的超级工程
- 内存学习之:大端模式、小端模式 详解
- 关于PGSQL的时间格式问题的报错总结
- FindBugs规则整理