测试数据库: 10.20.143.153;database=dbhnaoa3;uid=sa;pwd=tab,955@loct,目的优化 sp_CreatGetDocuToDoCount 存储过程。
拆分存储过程中的SQL语句,语句一:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT cnvcLeadEname,Count(cnvcLeadEname) AS iCount FROM DBHNAOA3_2010.dbo.tbAllot tbA
WHERE (tbA.cndReadDate IS NULL AND tbA.cnvcAllotType='0') OR (tbA.cndReadDate is not null and tbA.cnvcAllotType='1')
GROUP BY cnvcLeadEname
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
SQL Server 执行时间:
CPU 时间= 0 毫秒,耗费时间= 0 毫秒。
表'tbAllot'。扫描计数4,逻辑读77371 次,物理读0 次,预读77634 次。
SQL Server 执行时间:
CPU 时间= 1906 毫秒,耗费时间= 9009 毫秒。
(10611 行受影响)
查看执行计划,最终还是进行了表扫描。
从数据库中看到,cndReadDate和cnvcAllotType字段没有建立索引,并且cnvcLeadEname用于了Group by。那么给cndReadDate和cnvcAllotType字段建立索引。
并且将INTO TmpCreatGetDocuToDoCount1 改为 INTO #TmpCreatGetDocuToDoCount1。采用临时表的方式,不在当前库建立实体表,让Master去维护。语句改成如下的方式:
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT cnvcLeadEname,count(cnvcLeadEname) AS iCount INTO #TmpCreatGetDocuToDoCount1 FROM (
SELECT cnvcLeadEname FROM DBHNAOA3_2010.dbo.tbAllot tbA
WHERE (tbA.cndReadDate IS NULL AND tbA.cnvcAllotType='0')
UNION ALL
SELECT cnvcLeadEname FROM DBHNAOA3_2010.dbo.tbAllot tbA
WHERE (tbA.cndReadDate IS NOT NULL AND tbA.cnvcAllotType='1')
) tbB GROUP BY cnvcLeadEname
Drop table #TmpCreatGetDocuToDoCount1
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
结果如下:
SQL Server 执行时间:
CPU 时间= 0 毫秒,耗费时间= 0 毫秒。
表'#TmpCreatGetDocuToDoCount1__________________________________________________________________________________________000000000A98'。扫描计数0,逻辑读1 次,物理读0 次,预读0 次。
表'tbAllot'。扫描计数4,逻辑读30308 次,物理读0 次,预读0 次。
SQL Server 执行时间:
CPU 时间= 5162 毫秒,耗费时间= 5162 毫秒。
(10611 行受影响)
查看执行计划:
结果比较:
没有优化之前的SQL语句
表'tbAllot'。扫描计数4,逻辑读77371 次,物理读0 次,预读77634 次。
SQL Server 执行时间:
CPU 时间= 1906 毫秒,耗费时间= 9009 毫秒。
(10611 行受影响)
执行之后的SQL语句
表'tbAllot'。扫描计数4,逻辑读30308 次,物理读0 次,预读0 次。
SQL Server 执行时间:
CPU 时间= 5162 毫秒,耗费时间= 5162 毫秒。
(10611 行受影响)
经过小红哥的业务指导分析,SQL语句继续改成如下的格式:
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT cnvcLeadEname,COUNT(cnvcLeadEname) AS iCount FROM (
SELECT cnvcLeadEname FROM DBHNAOA3_2010.dbo.tbAllot WHERE cndReadDate IS NULL
UNION ALL
SELECT cnvcLeadEname FROM DBHNAOA3_2010.dbo.tbAllot WHERE cnvcAllotType='1'
) tbA GROUP BY cnvcLeadEname
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
SQL Server 执行时间:
CPU 时间= 0 毫秒,耗费时间= 0 毫秒。
表'tbAllot'。扫描计数2,逻辑读1514 次,物理读3 次,预读1516 次。
SQL Server 执行时间:
CPU 时间= 312 毫秒,耗费时间= 714 毫秒。
(10611 行受影响)
查看执行计划:
三次比较如下:
第一次:
SQL Server 执行时间:
CPU 时间= 0 毫秒,耗费时间= 0 毫秒。
表'tbAllot'。扫描计数4,逻辑读77371 次,物理读0 次,预读77634 次。
SQL Server 执行时间:
CPU 时间= 1906 毫秒,耗费时间= 9009 毫秒。
(10611 行受影响)
第二次:
表'tbAllot'。扫描计数4,逻辑读30308 次,物理读0 次,预读0 次。
SQL Server 执行时间:
CPU 时间= 5162 毫秒,耗费时间= 5162 毫秒。
(10611 行受影响)
第三次:
SQL Server 执行时间:
CPU 时间= 0 毫秒,耗费时间= 0 毫秒。
表'tbAllot'。扫描计数2,逻辑读1514 次,物理读3 次,预读1516 次。
SQL Server 执行时间:
CPU 时间= 312 毫秒,耗费时间= 714 毫秒。
(10611 行受影响)
结论:在数据一致的情况下,第三次优化的结果比第二次的结果有了更大幅度的提升。从执行计划可以看得出来,查询的环节更少了。有时候对查询的优化可以多从业务的角度考虑一下。