公文流转优化方案的第三弹，目标是公文跟踪中的“本部门呈报”功能。该功能文秘的使用频繁，而且性能影响很大，早就看它不顺眼了。终于狠下心来解决它。

使用集团机要秘书的账号，模拟查询。SQL SERVER PROFILE 的截图如下：

逻辑读1889568次，将近200万次的逻辑读。测试服务器  10.20.143.166

通过对存储过程的分析，找出核心的SQL语句，执行的效果如下：

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT cniId, cnvcTitle, cndSendTime, cnvcSecretaryKind, cnvcSecName, cnvcSendEname, cnvcSendCname,

      cnvcSendOrganCname, cnvcSendOrganId, cnvcSecretaryKind, cnvcEndflag,

      cnvcFileType,

          (SELECT TOP 1 cnvcLeadOrganCname FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId)  AS cnvcLeadOrganCname,

          (SELECT TOP 1 cnvcSpeedName FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId ORDER BY tbA.cniId DESC)  AS cnvcSpeedName,

         (SELECT TOP 1 cnvcSpeed FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId ORDER BY tbA.cniId DESC)  AS cnvcSpeed,

         (SELECT TOP 1 cndSpeedTime FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId ORDER BY tbA.cniId DESC)  AS cndSpeedTime,

          '' AS OweStatus,

       cnvcFileName

FROM tbCommonPart

WHERE cnvcSendOrganId IN (SELECT cniNodeID FROM tbCompany WHERE (cnvcOrganID LIKE '%0-1-%'))

      AND cniId IN

          (SELECT DISTINCT (tbApproval.cniCommonPartId)

         FROM tbCommonPart INNER JOIN

               tbApproval ON tbCommonPart.cniId = tbApproval.cniCommonPartId

         WHERE tbCommonPart.cnvcDeleteFlag = '0' AND

               tbApproval.cnvcSendEname <> '' AND cnvcReturnFlag = '0')  AND (cnvcTitle LIKE '%%')

     AND (DATEDIFF(day, cndSendTime, CAST('2011-6-13' AS DateTime)) >= 0 AND DATEDIFF(day,  cndSendTime, CAST('2011-3-15' AS DateTime)) <= 0)

    ORDER BY cnvcSpeed DESC,cndSpeedTime asc, cnvcSecretaryKind DESC, cndSendTime DESC

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

(58133 行受影响)

表'tbCommonPart'。扫描计数24，逻辑读取5160 次，物理读取5 次，预读2725 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'tbApproval'。扫描计数232537，逻辑读取1172990 次，物理读取72 次，预读10331 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'tbCompany'。扫描计数5，逻辑读取557 次，物理读取0 次，预读564 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

SQL Server 执行时间:

   CPU 时间= 2769 毫秒，占用时间= 8765 毫秒。

SQL Server 执行时间:

   CPU 时间= 0 毫秒，占用时间= 1 毫秒。

根据IO的分析，tbApproval表进行了100多万次逻辑读的操作。再继续查看一下执行计划。

从IO的逻辑读的次数和执行计划来看，键查找竟然损耗了20%的性能，这个查询基本全损耗在这四个键查找的地方。语句如下：

 (SELECT TOP 1 cnvcLeadOrganCname FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId)  AS cnvcLeadOrganCname,

 (SELECT TOP 1 cnvcSpeedName FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId ORDER BY tbA.cniId DESC)  AS cnvcSpeedName,

 (SELECT TOP 1 cnvcSpeed FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId ORDER BY tbA.cniId DESC)  AS cnvcSpeed,

 (SELECT TOP 1 cndSpeedTime FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId ORDER BY tbA.cniId DESC)  AS cndSpeedTime,

上面的四条语句每查询一次tbCommonPart表就要对tbApproval表查询四次，还加上排序。这块是性能损耗的巨头，放着后面解决它。先把其它方面进行优化。

Select count(*) FROM tbCommonPart

WHERE cnvcSendOrganId IN (SELECT cniNodeID FROM tbCompany WHERE (cnvcOrganID LIKE '%0-1-%'))

      AND cniId IN

          (SELECT DISTINCT (tbApproval.cniCommonPartId)

         FROM tbCommonPart INNER JOIN

               tbApproval ON tbCommonPart.cniId = tbApproval.cniCommonPartId

         WHERE tbCommonPart.cnvcDeleteFlag = '0' AND

               tbApproval.cnvcSendEname <> '' AND cnvcReturnFlag = '0')  AND (cnvcTitle LIKE '%%')

     AND (DATEDIFF(day, cndSendTime, CAST('2011-6-13' AS DateTime)) >= 0 AND DATEDIFF(day,  cndSendTime, CAST('2011-3-15' AS DateTime)) <= 0)

这里面用了两次IN，两次DATEDIFF表达式操作，两次%% 【Like语句是否属于SARG取决于所使用的通配符的类型。如：name like ‘张%’ ，这就属于SARG。而：name like ‘%张’ ,就不属于SARG。】的模糊查询，一次<>号【引擎将无法通过索引来确定将要命中的行数】的条件。这些都能导致引擎放弃使用索引而进行全表扫描。

1， 从业务上面来讲cnvcSendEname <> ''是永远不会成立的，将它取消。

2， 将两次IN操作使用JOIN来代替。

3， 取消DATTEDIFF函数，使用BETWEEN来代替。

4， cnvcTitle LIKE '%%' ，先判断cnvcTitle变量是否为空，如果为空则取消这个条件。

SELECT count(*) FROM tbCommonPart tbAA JOIN (SELECT DISTINCT (tbApproval.cniCommonPartId) AS cniId

         FROM tbCommonPart JOIN

               tbApproval ON tbCommonPart.cniId = tbApproval.cniCommonPartId

         WHERE tbCommonPart.cnvcDeleteFlag = '0' AND tbApproval.cnvcReturnFlag = '0') tbAB ON tbAA.cniId = tbAB.cniId

    JOIN tbCompany tbAC ON tbAA.cnvcSendOrganId = tbAC.cniNodeID WHERE tbAC.cnvcOrganID LIKE '%0-1-%'

AND cndSendTime BETWEEN '2011-3-15 00:00:00' AND '2011-6-13 23:59:59'

优化后在返回相同的数据的条件下，两次效果比较如下：

原来的SQL语句的执行结果：

(1 行受影响)

表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'tbApproval'。扫描计数1，逻辑读取9323 次，物理读取2 次，预读10419 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'tbCommonPart'。扫描计数13，逻辑读取2735 次，物理读取7 次，预读2882 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'tbCompany'。扫描计数1，逻辑读取557 次，物理读取0 次，预读564 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

SQL Server 执行时间:

   CPU 时间= 2703 毫秒，占用时间= 5907 毫秒。

SQL Server 执行时间:

   CPU 时间= 0 毫秒，占用时间= 1 毫秒。

优化之后的SQL语句执行结果：

(1 行受影响)

表'tbCompany'。扫描计数5，逻辑读取557 次，物理读取0 次，预读564 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'tbCommonPart'。扫描计数10，逻辑读取1110 次，物理读取4 次，预读408 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'tbApproval'。扫描计数10，逻辑读取2252 次，物理读取4 次，预读797 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

SQL Server 执行时间:

   CPU 时间= 518 毫秒，占用时间= 722 毫秒。

SQL Server 执行时间:

   CPU 时间= 0 毫秒，占用时间= 1 毫秒。

两次查询都使用了DBCC FREEPROCCACHE 和 DBCC DROPCLEANBUFFERS 清除缓存，所以数据的比较是有效的。优化了旁支，后面继续优化公文跟踪中最消耗资源的四条内嵌语句。