时不待我 天道酬勤

没有多少时间可以虚度了....

导航

公文流转SQL优化日志二

Posted on 2011-07-06 17:04  jadesun  阅读(262)  评论(0编辑  收藏  举报

测试数据库: 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 行受影响)

 

查看执行计划,最终还是进行了表扫描。


 

从数据库中看到,cndReadDatecnvcAllotType字段没有建立索引,并且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 行受影响)

 

结论:在数据一致的情况下,第三次优化的结果比第二次的结果有了更大幅度的提升。从执行计划可以看得出来,查询的环节更少了。有时候对查询的优化可以多从业务的角度考虑一下。