同事小红在周五时发了一个SQL优化的事项，因为周五在给新员工培训，所以到今天才给她完成了优化的工作。优化的思路后面再补上，先把优化的脚本发上来。
原来的语句执行时间
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT cnvcLeadEname
FROM tbApproval WHERE cndSpeedTime < cndReplyTime and cnvcReturnFlag = '0' AND cniCommonPartId in
( SELECT cniid FROM tbCommonPart WHERE cnvcDeleteFlag='0' AND cnvcEndFlag<>'0' and
cnvcfiletype Not In ( 0170,0115,0166,0160,0120,0121,0122,0123,0125,0126,0127, 0881,0883,0884,0885,0886,0887,0887,0888,0889,0893,0891)
AND datediff(d,cndEndTime,'2010-01-01' )<=0 AND datediff(d,cndEndTime,'2010-11-08' )>=0 )
AND(cnvcLeadEname != '999999999' AND cnvcLeadEname!='999999998')
AND(cnvcLeadOrganId in (SELECT cniNodeId FROM DBHNAOA3.dbo.tbCompany WHERE (cnvcOrganID LIKE '0-1-%') and cnvcflag=1 ) )
group by cnvcLeadEname having count(cnvcLeadEname)>=4
SET STATISTICS TIME OFF
DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。
DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。
(871 行受影响)
SQL Server 执行时间:
CPU 时间= 9531 毫秒，占用时间= 65547 毫秒。
优化之后的语句：
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SELECT cniid INTO #tempCommonPart FROM tbCommonPart WHERE
cnvcDeleteFlag='0' AND cnvcEndFlag <> '0' AND cnvcfiletype NOT IN ( 0170,0115,0166,0160,0120,0121,0122,0123,
0125,0126,0127, 0881,0883,0884,0885,0886,0887,0887,0888,0889,0893,0891)
AND cndEndTime BETWEEN '2010-01-01 00:00:00' AND '2010-11-08 23:59:59';
CREATE INDEX IX_Temp_vcTempTable3_CommonPartId ON #tempCommonPart(cniId);
SET STATISTICS TIME ON
SELECT A.cnvcLeadEname FROM #tempCommonPart C JOIN tbApproval A ON A.cniCommonPartId = C.cniId JOIN DBHNAOA3.dbo.tbCompany Company ON A.cnvcLeadOrganId = Company.cniNodeId
WHERE cndSpeedTime < cndReplyTime and cnvcReturnFlag = '0' AND (A.cnvcLeadEname != '999999999' AND A.cnvcLeadEname != '999999998')
AND Company.cnvcOrganID LIKE '0-1-%' AND Company.cnvcflag = 1 GROUP BY A.cnvcLeadEname HAVING COUNT(A.cnvcLeadEname) >= 4;
SET STATISTICS TIME OFF
DROP TABLE #tempCommonPart;
DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。
DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。
(394871 行受影响)
(871 行受影响)
SQL Server 执行时间:
CPU 时间= 1516 毫秒，占用时间= 4901 毫秒。
