周四在新大厦办公，正好遇上老董反映公文查询失败的问题。和小红监视了该查询语句在高峰期间，查询耗时需要40多秒，所以有必要整一整它了。
公文查询功能由于数据的原因，我不能在本地调试，全得由珊姐执行测试的思路。珊姐在执行老董的相关查询之后，返回的系统信息如下：
(28 行受影响)
表'tbCommonPart'。扫描计数1，逻辑读取1690869 次，物理读取0 次，预读1 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'Worktable'。扫描计数14，逻辑读取10570670 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'tmp40072411'。扫描计数14，逻辑读取6773 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
SQL Server 执行时间:
CPU 时间= 17406 毫秒，占用时间= 8751 毫秒。
娘的，tbCommonPart的逻辑读169万次，WorkTable表有1000多万次的逻辑读，执行时间累计26秒左右。
从存储过程中分析得到老董执行的存储过程核心语句有下面的四条。
1，SELECT distinct b.cnvcDeptid, cast(b.cnvcDeptid as varchar(300)) AS cnvcOrganID INTO tmp74790211 FROM tbUserRole a, tbRole b WHERE a.cnvcDisable = '1' AND b.cniRoleId = a.cniRoleId AND b.cnvcDisable = '1' AND cncJiYaoFlag !='0' AND a.cnvcUserId = '1000032237';
2，SELECT a.cnvcDeptid,b.cnvcOrganID INTO tmp67731953 FROM tmp74790211 a, tbCompany b WHERE a.cnvcDeptid = b.cniNodeID;
3，SELECT distinct a.cniId as cniCommonPartId INTO tmp93053275 FROM DBHNAOA3_2011.dbo.tbCommonPart a ,tmp74790211 b WHERE a.cnvcSendOrganId = b.cnvcDeptid;
4，SELECT distinct top 100 a.cniId, a.cniId as cniCommonPartId, a.cnvcFileType, a.cnvcFileName, a.cnvcTitle, a.cnvcSecName, a.cnvcSecretaryKind,a.cndSendTime, a.cnvcSendEname, a.cnvcSendCname, a.cndEndTime, a.cnvcSendOrganId, a.cnvcEndFlag FROM DBHNAOA3_2011.dbo.tbCommonPart a, tmp93053275 b WHERE a.cnvcEndFlag != '0' AND a.cniId = b.cniCommonPartId AND a.cnvcTitle like '%马国华%' AND a.cndEndTime >= '2011-01-01' AND a.cndEndTime < '2011-07-15' AND a.cnvcSecretaryKind not in ('0202','0203','0204') ORDER BY a.cndEndTime DESC
在执行第四条语句时出现了Worktable表，说明访问了大容量的临时表，造成了SQL优化器生成一个worktable来缓存中间查询结果，因为临时表是默认没有索引的。在执行a.cniId = b.cniCommonPartId就会产生 tmp93053275 表的全表扫描。
tmp93053275表的数据量从系统信息可以看出(560469 行受影响) ,该表有56万行数据参于全表扫描。根据笛卡儿乘积，WorkTable表就产生了1000多万次逻辑读。
这个性能优化的方法也很简单，就是给tmp93053275表的cniCommonPartId字段创建索引项。修改sp_QueryDoc这个存储过程，加入下面的语句。
set @tempSql = 'CREATE INDEX IX_Temp_vcTempTable3_CommonPartId ON '+@vcTmpTable3+'(cniCommonPartId)';
exec(@tempSql);
重新让珊姐执行老董的查询语句，反馈的信息如下：
(28 行受影响)
表'tmp95890048'。扫描计数28，逻辑读取114 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'tbCommonPart'。扫描计数1，逻辑读取1690742 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
SQL Server 执行时间:
CPU 时间= 2828 毫秒，占用时间= 2833 毫秒。
总结：存储过程经过优化之后，WorkTable表和1000多万次的逻辑读没有了，查询时间也从25秒降到了4-5秒左右。但是tbCommonPart表还有169万次的逻辑读，因为珊姐和我的环境里面观察到的执行计划不同。这个要留到后面有时间时再去优化了。
