原来的语句执行时间

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 毫秒。