测试数据库: 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 行受影响)
*****************************************************************
结论:temp2151427318227041856Organ,temp2151427318227041856DocId这两张表和tbApproval的扫描影响了性能。
而且不断的create和drop表,在并发的时侯有可能出现Create表或Drop表的失败。如果非得create表,建议在存储过程中使用Declare tb @table ()表变量。
*****************************************************************
我们的目标(不是没有蛀牙):
解决select into 生成的两张临时表 temp2151427318227041856Organ、temp2151427318227041856DocId。并且提高查询效率。
第一步分析:
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 行受影响)
***********************************************************
结论:差别真的很大。。。。。。。。。。哈哈
***********************************************************