2015-08-24--刘松灵--SQL查询模板优化

疫控SQL查询模板优化1）以件计酬模板(模板号:315-317)SQL句体见后语句附（1）。该语句所有的字段都是取自视图:VW_ORDERSINFO_FOR_ZD_BREEDINGINFO_JiaQin,gai，该视图嵌套了视图VW_ORDERSINFO_FOR_ZD_BREEDINGINFO，VW_RESULTS_FINALINFO_ZD，视图主要联接了以下几个表:FOLDERS，ORDER,ORDTASK,RESULTS,BREEDING_INFO，IMMUNITY等几个表，查看执行计划得知，数据库主要在涉及几个表的扫描及聚族索引时占据了大半资源开销。根据各个查询及视图联接条件，我们采用给表ORDERS,RESULTS等几个表增加联合索引的方式，让其走索引以减少表及聚族索引扫描，减少资源开销。在FOLDERS表上增加非聚族索引索引INDEX_FOLDERS_YJJF：CREATENONCLUSTEREDINDEX[INDEX_FOLDERS_820]ON[FOLDERS]([SMALLTYPE]ASC,[QCTYPE]ASC,[ADMINISTRATIVEDIVISIONS3]ASC,[FLDSTS]ASC,[FOLDERNO]ASC,[OPERATIONTYPE]ASC)WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF)ON[PRIMARY]在ORDERS上增加非聚族索引:INDEX_ORDERS_YJJF:CREATENONCLUSTEREDINDEX[INDEX_ORDERS_820]ON[ORDERS]([ORDNO]ASC,[FOLDERNO]ASC,[ISSELECTED]ASC,[SAMPLINGTIME]ASC,[BNAME]ASC)INCLUDE([ORDGROUP])WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF)ON[PRIMARY]在IMMUNITY上增加非聚族索引:INDEX_IMMUNITY_YJJFCREATENONCLUSTEREDINDEX[INDEX_IMMUNITY_YJJF]ON[IMMUNITY]([BREEDINGID]ASC)INCLUDE([ORDNO],[FOLDERNO],[ASSOCIATEDTESTS])WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF)ON[PRIMARY]在ORDTASK上增加非聚族索引:INDEX_ORDTASK_YJJFCREATENONCLUSTEREDINDEX[INDEX_ORDTASK_YJJF]ON[dbo].[ORDTASK]([DISPINREPORT]ASC,[FOLDERNO]ASC,[TS]ASC,[ISTRUETESTED]ASC,[TESTCODE]ASC,[ORDNO]ASC,[ISHAVEIMMUNE]ASC)INCLUDE([TESTNO],[QUALIFIEDRESULT])WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF)ON[PRIMARY]使用setstatisticstimeon,开启客户端计时，前后效果如下:优化前：SQLServer分析和编译时间:CPU时间=343毫秒，占用时间=1710毫秒。警告:聚合或其他SET操作消除了Null值。(9行受影响)SQLServer执行时间:CPU时间=983毫秒，占用时间=1984毫秒。优化后：SQLServer分析和编译时间:CPU时间=437毫秒，占用时间=792毫秒。警告:聚合或其他SET操作消除了Null值。(9行受影响)SQLServer执行时间:CPU时间=437毫秒，占用时间=595毫秒。可以看出前后查询销量有了较明显的提升。2）检测明细表优化（模板号：328-341）SQL句体见后语句附（2）。该查询用到了VW_MonitoringData_Detail视图，主要涉及FOLDERS,ORDERS,RESULTS等等几个表，分析过程同上。根据视图联接及查询条件，增加以下几个索引：ORDTASK上增加非聚族索引：CREATENONCLUSTEREDINDEX[INDEX_ORDTASK_MONDETAIL]ON[ORDTASK]([ISTRUETESTED]ASC,[DISPINREPORT]ASC,[TS]ASC,[TESTCODE]ASC)INCLUDE([ANALYZEDDATE],[FOLDERNO],[ORDNO],[POSITIVERESULT],[QUALIFIEDRESULT],[ISHAVEIMMUNE])WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF)ON[PRIMARY]FOLDERS上增加非聚族索引：CREATENONCLUSTEREDINDEX[INDEX_FOLDERS_MONDETAIL]ON[FOLDERS]([DEPT]ASC,[OPERATIONTYPE]ASC,[FOLDERNO]ASC,[ADMINISTRATIVEDIVISIONS2]ASC)WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF)ON[PRIMARY]前后效果如下:优化前：SQLServer分析和编译时间:CPU时间=188毫秒，占用时间=2563毫秒。警告:聚合或其他SET操作消除了Null值。(8行受影响)SQLServer执行时间:CPU时间=795毫秒，占用时间=2220毫秒。优化后：SQLServer分析和编译时间:CPU时间=203毫秒，占用时间=2431毫秒。警告:聚合或其他SET操作消除了Null值。(8行受影响)SQLServer执行时间:CPU时间=437毫秒，占用时间=870毫秒。可以看出优化前后查询效率有所提升。3）VW_Statistics_YKZD_ResultDetail视图查询优化。测试SQL句体见后语句附（3）。该视图嵌套视图VW_FOLDERNO_ORDTASK_BREDINGINFO_ZD，涉及模板详细信息及三间分布（模板号：225-276），（sql句体见sql3）。分析同上，根据视图联接及查询条件优化方式如下：BREEDING_INFO创建非聚族索引：CREATENONCLUSTEREDINDEX[INDEX_BREEDING_INFO_TJCX]ON[BREEDING_INFO]([BNAME]ASC,[RASCLIENTID]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]FOLDERS创建非聚族索引：CREATENONCLUSTEREDINDEX[_INDEX_FOLDERS_TJCX]ON[FOLDERS]([DEPT]ASC,[LOGDATE]ASC,[SMALLTYPE]ASC,[OPERATIONTYPE]ASC,[QCTYPE]ASC,[FLDSTS]ASC,[FOLDERNO]ASC)INCLUDE([RASCLIENTID],[ADMINISTRATIVEDIVISIONS2])WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ORDERS创建非聚族索引：CREATENONCLUSTEREDINDEX[INDEX_ORDERS_TJCX]ON[ORDERS]([ORDNO]ASC,[FOLDERNO]ASC,[ISSELECTED]ASC,[SAMPLINGTIME]ASC,[BNAME]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]优化前后效果如下:优化前：SQLServer分析和编译时间:CPU时间=187毫秒，占用时间=2418毫秒。警告:聚合或其他SET操作消除了Null值。(73行受影响)SQLServer执行时间:CPU时间=140毫秒，占用时间=2729毫秒。优化后：SQLServer分析和编译时间:CPU时间=156毫秒，占用时间=2175毫秒。警告:聚合或其他SET操作消除了Null值。(73行受影响)SQLServer执行时间:CPU时间=94毫秒，占用时间=730毫秒。可以看出查询效率有了一定提升。同时其他依据该视图的查询也有所提升。附依据模板提取的部分测试SQL1)select[乡镇],casewhen([H5抗体-样品数])=0then'/'elseconvert(varchar,convert(decimal(12,2),[H5抗体-个体合格数]*100.0/[H5抗体-样品数]))+'%'endas[H5抗体-个体合格率],casewhen([H5抗体-场群总数])=0then'/'elseconvert(varchar,convert(decimal(12,2),[H5抗体-场群合格数]*100.0/[H5抗体-场群总数]))+'%'endas[H5抗体-场群合格率],casewhen([O型抗体-样品数])=0then'/'elseconvert(varchar,convert(decimal(12,2),[O型抗体-个体合格数]*100.0/[O型抗体-样品数]))+'%'endas[O型抗体-个体合格率],casewhen([O型抗体-场群总数])=0then'/'elseconvert(varchar,convert(decimal(12,2),[O型抗体-场群合格数]*100.0/[O型抗体-场群总数]))+'%'endas[O型抗体-场群合格率],casewhen([猪瘟抗体-样品数])=0then'/'elseconvert(varchar,convert(decimal(12,2),[猪瘟抗体-个体合格数]*100.0/[猪瘟抗体-样品数]))+'%'endas[猪瘟抗体-个体合格率],casewhen([猪瘟抗体-场群总数])=0then'/'elseconvert(varchar,convert(decimal(12,2),[猪瘟抗体-场群合格数]*100.0/[猪瘟抗体-场群总数]))+'%'endas[猪瘟抗体-场群合格率],casewhen([小反刍抗体-样品数])=0then'/'elseconvert(varchar,convert(decimal(12,2),[小反刍抗体-个体合格数]*100.0/[小反刍抗体-样品数]))+'%'endas[小反刍抗体-个体合格率],casewhen([小反刍抗体-场群总数])=0then'/'elseconvert(varchar,convert(decimal(12,2),[