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
*/