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   
        (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=0
select 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=0
select distinct left(bt,4) as bbt,t_table_id into #temp2 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
select right(bt,5) as bbt,* into #temp3 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
select distinct right(bt,5) as bbt,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','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);
   --34
print convert(varchar,getdate(),9)   
/*
drop table #temp1   
drop table #temp2
drop table #temp3
drop table #temp4
*/
   
   

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6517/viewspace-669618/，如需轉載，請註明出處，否則將追究法律責任。