时不待我 天道酬勤

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

导航

公文流转SQL优化日志一

Posted on 2011-07-06 16:57  jadesun  阅读(418)  评论(0编辑  收藏  举报

测试数据库: 10.2.57.87\SL2;database=dbhnaoa3_2009;uid=sa;pwd=tab,955@loct

 

***********************************************************************************

需要优化的语句

 

SET STATISTICS TIME ON

 

SELECT cniNodeId into temp2151427318227041856Organ FROM DBHNAOA3.dbo.tbCompany WHERE (cnvcOrganID LIKE '0-1-8-47-%')

SELECT distinct (cniCommonpartId) into temp2151427318227041856DocId  FROM tbApproval WHERE cnvcReturnFlag = '0'

         And cnvcLeadOrganId in  (SELECT cniNodeId FROM temp2151427318227041856Organ )

 

SELECT count(cniId) AS AllTranFile  FROM tbCommonPart WHERE cniid IN ( SELECT cniCommonpartId FROM temp2151427318227041856DocId )

AND cnvcDeleteFlag='0' AND cnvcEndFlag<>'0' AND cniId is not null AND datediff(d,cndEndTime,'2009-1-1' )<=0 

AND datediff(d,cndEndTime,'2009-9-1' )>=0  And cnvcfiletype Not In ( 0170,0115,0166,0160,0120,0121,0122,0123,0125,0126,0127 )

 

DROP TABLE temp2151427318227041856Organ     

DROP TABLE temp2151427318227041856DocId

 

SET STATISTICS TIME OFF

 

'temp2151427318227041856Organ'。扫描计数 0,逻辑读 1 次,物理读 0 次,预读 0 次。

'tbCompany'。扫描计数 1,逻辑读 2 次,物理读 0 次,预读 0 次。

 

(81 行受影响)

'temp2151427318227041856Organ'。扫描计数 1,逻辑读 2 次,物理读 0 次,预读 0 次。

'tbApproval'。扫描计数 4,逻辑读 70585 次,物理读 0 次,预读 0 次。

'temp2151427318227041856DocId'。扫描计数 0,逻辑读 1 次,物理读 0 次,预读 0 次。

 

(5059 行受影响)

'temp2151427318227041856DocId'。扫描计数 1,逻辑读 10 次,物理读 0 次,预读 0 次。

'tbCommonPart'。扫描计数 1,逻辑读 1805 次,物理读 0 次,预读 0 次。

 

(1 行受影响)

 

*****************************************************************

 

结论:temp2151427318227041856Organtemp2151427318227041856DocId这两张表和tbApproval的扫描影响了性能。

而且不断的createdrop表,在并发的时侯有可能出现Create表或Drop表的失败。如果非得create表,建议在存储过程中使用Declare tb @table ()表变量。

 

*****************************************************************

 

我们的目标(不是没有蛀牙):

       解决select into 生成的两张临时表 temp2151427318227041856Organtemp2151427318227041856DocId。并且提高查询效率。

 

 

第一步分析:

SELECT cniNodeId into temp2151427318227041856Organ FROM DBHNAOA3.dbo.tbCompany WHERE (cnvcOrganID LIKE '0-1-8-47-%')

 

WHERE (cnvcOrganID LIKE '0-1-8-47-%')  <---- %在后面,这个语句使用了索引,性能上不会有多少影响

 

SET STATISTICS IO ON

SELECT cniNodeId FROM DBHNAOA3.dbo.tbCompany WHERE (cnvcOrganID LIKE '0-1-8-47-%')

SET STATISTICS IO OFF

 

'tbCompany'。扫描计数 1,逻辑读 2 次,物理读 0 次,预读 0 次。

 

(81 行受影响)

 

***********************************************************

 

结论:这个SQL语句使用了索引,从逻辑读的次数来看性能非常的好,那么我们就不再create temp2151427318227041856Organ 这张临时表,而是直接调用查询语句

 

***********************************************************

 

第二步分析:

 

测试原来的语句:

SET STATISTICS IO ON

 

SELECT cniNodeId into temp2151427318227041856Organ FROM DBHNAOA3.dbo.tbCompany WHERE (cnvcOrganID LIKE '0-1-8-47-%')

SELECT distinct (cniCommonpartId) into temp2151427318227041856DocId  FROM tbApproval WHERE cnvcReturnFlag = '0' And cnvcLeadOrganId in 

         (SELECT cniNodeId FROM temp2151427318227041856Organ )

 

SET STATISTICS IO OFF

 

'temp2151427318227041856Organ'。扫描计数 0,逻辑读 1 次,物理读 0 次,预读 0 次。

'tbCompany'。扫描计数 1,逻辑读 2 次,物理读 0 次,预读 0 次。

 

(81 行受影响)

'temp2151427318227041856Organ'。扫描计数 1,逻辑读 2 次,物理读 0 次,预读 0 次。

'tbApproval'。扫描计数 4,逻辑读 70585 次,物理读 0 次,预读 0 次。

'temp2151427318227041856DocId'。扫描计数 0,逻辑读 1 次,物理读 0 次,预读 0 次。

 

(5059 行受影响)

 

语句优化,避免产生 temp2151427318227041856DocId 表,修改之后的语句为:

SET STATISTICS IO ON

 

SELECT DISTINCT(a.cniCommonpartId) FROM

         (SELECT cniNodeId FROM DBHNAOA3.dbo.tbCompany company WHERE (cnvcOrganID LIKE '0-1-8-47-%')) b

                   JOIN tbApproval a ON b.cniNodeId = a.cnvcLeadOrganId

                            WHERE cnvcReturnFlag = '0'

 

SET STATISTICS IO OFF

 

'tbCompany'。扫描计数 1,逻辑读 2 次,物理读 0 次,预读 0 次。

'tbApproval'。扫描计数 4,逻辑读 70585 次,物理读 591 次,预读 3048 次。

 

(5059 行受影响)

 

以之前的结果进行比较,不生成临时表了,但是 tbApproval 表的逻辑读还是 70585 次,并且有物理读和预读的情况,没有采用数据库的缓存

再查看一下执行的时间:

 

SET STATISTICS TIME ON

 

SELECT DISTINCT(a.cniCommonpartId) FROM

         (SELECT cniNodeId FROM DBHNAOA3.dbo.tbCompany company WHERE (cnvcOrganID LIKE '0-1-8-47-%')) b

                   JOIN tbApproval a ON b.cniNodeId = a.cnvcLeadOrganId

                            WHERE cnvcReturnFlag = '0'

 

SET STATISTICS TIME OFF

 

SQL Server 执行时间:

   CPU 时间 = 0 毫秒,耗费时间 = 0 毫秒。

 

SQL Server 执行时间:

   CPU 时间 = 4937 毫秒,耗费时间 = 1527 毫秒。

 

(5059 行受影响)

 

---------------------------------------

 

继续修改语句如下,并且查看逻辑读和执行时间:

SET STATISTICS IO ON

 

SELECT DISTINCT(a.cniCommonpartId) FROM

         (SELECT cniNodeId FROM DBHNAOA3.dbo.tbCompany company WHERE (cnvcOrganID LIKE '0-1-8-47-%')) b

                   LEFT OUTER JOIN tbApproval a ON CAST(b.cniNodeId AS varchar(30)) = a.cnvcLeadOrganId

                            WHERE cnvcReturnFlag = '0'

 

SET STATISTICS IO OFF

 

'tbApproval'。扫描计数 81,逻辑读 56136 次,物理读 0 次,预读 0 次。

'tbCompany'。扫描计数 1,逻辑读 2 次,物理读 0 次,预读 0 次。

 

(5059 行受影响)

 

修改之后的语句,逻辑读比之上的语句减少了将2万次。查看一下执行时间

 

SET STATISTICS TIME ON

 

SELECT DISTINCT(a.cniCommonpartId) FROM

         (SELECT cniNodeId FROM DBHNAOA3.dbo.tbCompany company WHERE (cnvcOrganID LIKE '0-1-8-47-%')) b

                   LEFT OUTER JOIN tbApproval a ON CAST(b.cniNodeId AS varchar(30)) = a.cnvcLeadOrganId

                            WHERE cnvcReturnFlag = '0'

 

SET STATISTICS TIME OFF

 

SQL Server 执行时间:

   CPU 时间 = 0 毫秒,耗费时间 = 0 毫秒。

 

SQL Server 执行时间:

   CPU 时间 = 203 毫秒,耗费时间 = 226 毫秒。

 

(5059 行受影响)

 

修改之后的语句执行时间比原来的少了 1200 多毫秒,很大的程度上提升了性能。

 

***********************************************************

 

结论:重点在 ON CAST(b.cniNodeId AS varchar(30)) = a.cnvcLeadOrganId 这个语句,cnvcLeadOrganId tbApproval 是建立索引的,但是字段的类型是varchar

company 表的字段类型为 cniNodeId int型。int = varchar 。嘿嘿................

 

***********************************************************

 

 

第三步套入原来的语句,将两条语句的执行时间进行比较

 

原来的语句

 

SET STATISTICS TIME ON

 

SELECT cniNodeId into temp2151427318227041856Organ FROM DBHNAOA3.dbo.tbCompany WHERE (cnvcOrganID LIKE '0-1-8-47-%')

SELECT distinct (cniCommonpartId) into temp2151427318227041856DocId  FROM tbApproval WHERE cnvcReturnFlag = '0'

         And cnvcLeadOrganId in  (SELECT cniNodeId FROM temp2151427318227041856Organ )

 

SELECT count(cniId) AS AllTranFile  FROM tbCommonPart WHERE cniid IN ( SELECT cniCommonpartId FROM temp2151427318227041856DocId )

AND cnvcDeleteFlag='0' AND cnvcEndFlag<>'0' AND cniId is not null AND datediff(d,cndEndTime,'2009-1-1' )<=0 

AND datediff(d,cndEndTime,'2009-9-1' )>=0  And cnvcfiletype Not In ( 0170,0115,0166,0160,0120,0121,0122,0123,0125,0126,0127 )

 

DROP TABLE temp2151427318227041856Organ     

DROP TABLE temp2151427318227041856DocId

 

SET STATISTICS TIME OFF

 

执行时间:

 

 

SQL Server 执行时间:

   CPU 时间 = 0 毫秒,耗费时间 = 0 毫秒。

 

SQL Server 执行时间:

   CPU 时间 = 0 毫秒,耗费时间 = 22 毫秒。

 

(81 行受影响)

 

SQL Server 执行时间:

   CPU 时间 = 5156 毫秒,耗费时间 = 1644 毫秒。

 

(5059 行受影响)

 

SQL Server 执行时间:

   CPU 时间 = 1984 毫秒,耗费时间 = 3039 毫秒。

 

(1 行受影响)

 

SQL Server 执行时间:

   CPU 时间 = 0 毫秒,耗费时间 = 5 毫秒。

 

SQL Server 执行时间:

   CPU 时间 = 0 毫秒,耗费时间 = 6 毫秒。

 

 

--------------------------------------------------------------------------------

 

优化之后的语句

 

SET STATISTICS TIME ON

 

SELECT COUNT(cniId) AS AllTranFile FROM tbCommonPart c JOIN

 

         (SELECT DISTINCT(a.cniCommonpartId) AS cniCommonpartId FROM

                   (SELECT cniNodeId FROM DBHNAOA3.dbo.tbCompany company WHERE (cnvcOrganID LIKE '0-1-8-47-%')) b

                            LEFT OUTER JOIN tbApproval a ON CAST(b.cniNodeId AS varchar(30)) = a.cnvcLeadOrganId

                                     WHERE cnvcReturnFlag = '0' ) d ON c.cniid = d.cniCommonpartId

 

                   AND cnvcDeleteFlag='0' AND cnvcEndFlag<>'0' AND cniId is not null AND datediff(d,cndEndTime,'2009-1-1' )<=0 

                   AND datediff(d,cndEndTime,'2009-9-1' )>=0  And cnvcfiletype Not In ( 0170,0115,0166,0160,0120,0121,0122,0123,0125,0126,0127 )

 

SET STATISTICS TIME OFF

 

执行时间:

 

SQL Server 执行时间:

   CPU 时间 = 0 毫秒,耗费时间 = 0 毫秒。

 

SQL Server 执行时间:

   CPU 时间 = 296 毫秒,耗费时间 = 306 毫秒。

 

(1 行受影响)

 

***********************************************************

结论:差别真的很大。。。。。。。。。。哈哈

***********************************************************