继续调优，今天上午分析了以下一条处理时间达40秒的SQL语句select * from table where T_table_ID in ( select distinct s.t_table_id from ( select distinct a.t_table_id,a.bt from (select left(bt,4) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) a, (select distinct left(bt,4) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) b where b.bbt like a.bbt and a.t_table_id<>b.t_table_id and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%' and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%' and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%' and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%' -- order by a.bt union all select distinct a.t_table_id,a.bt from (select right(bt,5) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) a, (select distinct right(bt,5) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) b where b.bbt like a.bbt and a.t_table_id<>b.t_table_id and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%' and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%' and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%' and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%' and a.bbt not like '%'+(select right(convert(varchar(10),getdate()-1,20),2)+')') +'%' and b.bbt not like '%'+(select right(convert(varchar(10),getdate()-1,20),2)+')') +'%' ) s )order by bt 基本上可以认为是对同一张表的反复操作，而且语句中夹杂了太多的全表扫描SQLServer的执行计划我个人认为图形化界面固然是好，但是有些时候对于量化的I/O,CPU,COST输出却很不直观，此外像该SQL这样的执行计划，估计1600*1200的整个屏幕都无法显示，可以认为基本是没法看的只能将SQL分解成若干小SQL，逐步找到瓶颈所在，例如select left(bt,4) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0select distinct left(bt,4) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0这两个语句的执行都非常快，并且结果集也比较小，但是两条语句合并后并加上相关条件就非常缓慢。干脆直接构建两个临时表，反正都是全表扫描，用两个临时表做相互的join，测试之后发现只需要1秒再构建下面的两个SQL临时表，也做同样的测试最后再全部合并到一起进行测试，发现也就是2~3秒实际上还可以再优化一些临时表的构建，但效果达到了也就不愿意尝试了也尝试过用CTE，不过似乎效果不佳以下为优化后的SQL样例/*with temp1 as(select left(bt,4) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0),temp2 as(select distinct left(bt,4) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0),temp3 as(select left(bt,5) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0),temp4 as(select distinct left(bt,5) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0)*/print convert(varchar,getdate(),9)select left(bt,4) as bbt,* into #temp1 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0select distinct left(bt,4) as bbt,t_table_id into #temp2 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0select right(bt,5) as bbt,* into #temp3 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0select distinct right(bt,5) as bbt,t_table_id into #temp4 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0select (select ms from xtclb where dm=lmxz and lb in (130,131) ) as '栏目选择', bt,mtly,czy from table where T_table_ID in ( select distinct s.t_table_id from ( select distinct a.t_table_id,a.bt from #temp1 a, #temp2 b where b.bbt like a.bbt and a.t_table_id<>b.t_table_id and a.bbt not in ('aaaa','bbbb','cccc','dddd','eeee','ffff') and b.bbt not in ('aaaa','bbbb','cccc','dddd','eeee','ffff') union all select distinct a.t_table_id,a.bt from #temp3 a, #temp4 b where b.bbt like a.bbt and a.t_table_id<>b.t_table_id and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%' and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%' and a.bbt not like '%'+(select right(convert(varchar(10),getdate()-1,20),2)+')') +'%' and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%' and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%' and b.bbt not like '%'+(select right(convert(varchar(10),getdate()-1,20),2)+')') +'%' ) s )order by bt --OPTION (loop join); --34print convert(varchar,getdate(),9) /*drop table #temp1 drop table #temp2drop table #temp3drop table #temp4*/
本文转自baoqiangwang51CTO博客，原文链接：http://blog.51cto.com/baoqiangwang/361039，如需转载请自行联系原作者
