SQLServer性能优化之活用临时表

概述继续调优，今天上午分析了以下一条处理时间达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            (sel 继续调优，今天上午分析了以下一条处理时间达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,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,          (select distinct right(bt,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),2)+')') +'%'          ) s       )order by bt   基本上可以认为是对同一张表的反复 *** 作，而且语句中夹杂了太多的全表扫描 sqlServer的执行计划我个人认为图形化界面固然是好，但是有些时候对于量化的I/O,cpu,COST输出却很不直观，此外像该sql这样的执行计划，估计1600*1200的整个屏幕都无法显示，可以认为基本是没法看的 只能将sql分解成若干小sql，逐步找到瓶颈所在，例如 select left(bt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0 select distinct left(bt,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,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0),temp2 as (select distinct left(bt,t_table_ID from table where fsrq>getdate()-1 and gkbz=1 and scbz=0),temp3 as (select left(bt,temp4 as (select distinct left(bt,t_table_ID from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) */ print convert(varchar,getdate(),9) select left(bt,* into #temp1 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0 select distinct left(bt,t_table_ID into #temp2 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0 select right(bt,* into #temp3 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0 select distinct right(bt,t_table_ID into #temp4 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0 select (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','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),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),2)+')') +'%'          ) s       )order by bt      --OPTION (loop join);    --34 print convert(varchar,9)    /* drop table #temp1    drop table #temp2 drop table #temp3 drop table #temp4 */         总结

以上是内存溢出为你收集整理的SQLServer性能优化之活用临时表全部内容，希望文章能够帮你解决SQLServer性能优化之活用临时表所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。