SQL Server：使用 case 語句提高 CTE 性能

我需要你的幫助，我需要一些指導來提高以下給定視圖的性能。

我有一個用以下程式碼編寫的視圖：

with timeframes  as
(
select p.SEARCH_NUM, 
case   when p.FROM_DATE is not null then p.FROM_DATE
      when p.FROM_DATE is null and P.SEARCH_DAYS is not null and p.TO_DATE is not null then DATEADD(day,p.SEARCH_DAYS*-1,p.TO_DATE)
      when p.FROM_DATE is null and P.SEARCH_DAYS is not null and p.TO_DATE is null then DATEADD(day,p.SEARCH_DAYS*-1,GetDate()) 
      when p.FROM_DATE is null and P.SEARCH_DAYS is null and p.TO_DATE is not null and p.DURATION = 'Yearly' then DATEADD(year,-1,p.TO_DATE)
      when p.FROM_DATE is null and P.SEARCH_DAYS is null and p.TO_DATE is null and p.DURATION = 'Yearly' then DATEADD(year,-1,GetDate())
      when p.FROM_DATE is null and P.SEARCH_DAYS is null and p.TO_DATE is not null and p.DURATION = 'Monthly' then DATEADD(month,-1,p.TO_DATE)
      when p.FROM_DATE is null and P.SEARCH_DAYS is null and p.TO_DATE is null and p.DURATION = 'Monthly' then DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
      when p.FROM_DATE is null and P.SEARCH_DAYS is null and p.TO_DATE is not null and p.DURATION = 'Weekly' then DATEADD(day,-7,p.TO_DATE)
      when p.FROM_DATE is null and P.SEARCH_DAYS is null and p.TO_DATE is null and p.DURATION = 'Weekly' then DATEADD(day,-7,GetDate())
      else DATEADD(month,-1,GetDate())
 end as FROM_DATE
 ,case when p.TO_DATE is not null then p.TO_DATE
   when p.TO_DATE is null and p.DURATION = 'Monthly' then DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)
      else GetDate()
 end as TO_DATE
from dbo.parmeters_table as p
)
,
transactions as
(
 select tm.SEARCH_NUM, tr.id from dbo.ixf_transaction tr
 inner join timeframes tm on tr.transaction_date between tm.FROM_DATE and tm.TO_DATE
)
,
searchResults AS
(
select DISTINCT
 t.SEARCH_NUM
 ,trx.id as 'trx_id_FK'
 ,trx.institution_FK
 ,trx.branch_FK
 ,branch.code as 'branch_number'
 ,trx.account_FK
 ,trx.transaction_date as 'trx_date'
 ,case when trx.type_of_transaction = 'I' or trx.type_of_transaction = 'B'
   then trx.base_currency_amount else 0 end as 'cash_in'
 ,case when trx.type_of_transaction = 'O' or trx.type_of_transaction = 'B'
   then trx.base_currency_amount else 0 end as 'cash_out'  
 ,case when trx.type_of_transaction = 'B'
   then trx.base_currency_amount else 0 end as 'curr_exchange'  
 ,trx.base_currency_amount    
 ,trx.type_of_transaction
 ,trx.teller_id
 ,trx.unique_trans_id
 ,trx.serial_number
 ,trx.foreign_amount
 ,trx.country_of_currency_FK
 ,trx.flex_1
 ,trx.flex_2
 ,trx.customer_FK
 ,ttr.code as 'trx_code'
 ,ttr.description as 'trx_description'
 ,ttr.irs_transaction_id
,cust.full_name 
,str(cust.web_reference_id) as 'web_reference_id' 
,conben.customer_cif 
,conben.id_number 
,conben.id_type
,conben.other_description as 'id_type_other_description'
,conben.customer_tin 
,conben.cust_type_fk as 'TIN_Type'
,ctrtx.is_teller
from ixf_transaction trx
 inner join transactions t on trx.id = t.id
 inner join type_ref ttr on ttr.id=trx.transaction_type_FK
 inner join unit branch on branch.id=trx.branch_FK and branch.object_type='Branch'
 left join  cust on cust.id = trx.customer_FK
 left join  cashtx_cus conben on conben.transaction_id = trx.unique_trans_id    and conben.customer_FK = trx.customer_FK
 left join  trans ctrtx on ctrtx.cash_transaction_fk = trx.id
) 

 select trx_id_fk 
 ,case when account_FK is not null then (select count(1) from searchresults a where a.account_fk = searchresults.account_fk and a.SEARCH_NUM = searchresults.SEARCH_NUM) 
  when customer_cif is not null then (select count(1) from searchresults a where a.customer_cif = searchresults.customer_cif and a.SEARCH_NUM = searchresults.SEARCH_NUM) 
  when customer_tin is not null then (select count(1) from searchresults a where a.customer_tin = searchresults.customer_tin and a.TIN_Type = searchresults.TIN_Type and a.SEARCH_NUM = searchresults.SEARCH_NUM) 
  when ID_NUMBER is not null then (select count(1) from searchresults a where a.ID_NUMBER = searchresults.ID_NUMBER and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  else (select count(1) from searchresults a where a.SEARCH_NUM = searchresults.SEARCH_NUM)
  end as 'trx_per_period'
 ,case when account_FK is not null then (select count(1) from searchresults a where a.account_FK = searchresults.account_FK and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM) 
  when customer_cif is not null then (select count(1) from searchresults a where a.customer_cif = searchresults.customer_cif and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM) 
  when customer_tin is not null then (select count(1) from searchresults a where a.customer_tin = searchresults.customer_tin and a.TIN_Type = searchresults.TIN_Type and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when ID_NUMBER is not null then (select count(1) from searchresults a where a.ID_NUMBER = searchresults.ID_NUMBER and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  else (select count(1) from searchresults a where a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  end as 'trx_per_day'
 ,case when account_FK is not null then (select sum(cash_in) from searchresults a where a.account_FK = searchresults.account_FK and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when customer_cif is not null then (select sum(cash_in) from searchresults a where a.customer_cif = searchresults.customer_cif and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when customer_tin is not null then (select sum(cash_in) from searchresults a where a.customer_tin = searchresults.customer_tin and a.TIN_Type = searchresults.TIN_Type and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when ID_NUMBER is not null then (select sum(cash_in) from searchresults a where a.ID_NUMBER = searchresults.ID_NUMBER and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  else (select sum(cash_in) from searchresults a where a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  end as 'total_per_day_cash_in'
 ,case when account_FK is not null then (select sum(cash_out) from searchresults a where a.account_FK = searchresults.account_FK and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when customer_cif is not null then (select sum(cash_out) from searchresults a where a.customer_cif = searchresults.customer_cif and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when customer_tin is not null then (select sum(cash_out) from searchresults a where a.customer_tin = searchresults.customer_tin and a.TIN_Type = searchresults.TIN_Type and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when ID_NUMBER is not null then (select sum(cash_out) from searchresults a where a.ID_NUMBER = searchresults.ID_NUMBER and a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  else (select sum(cash_out) from searchresults a where a.trx_date = searchresults.trx_date and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  end as 'total_per_day_cash_out'
 ,case when account_FK is not null then (select sum(cash_in) from searchresults a where a.account_FK = searchresults.account_FK and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when customer_cif is not null then (select sum(cash_in) from searchresults a where a.customer_cif = searchresults.customer_cif and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when customer_tin is not null then (select sum(cash_in) from searchresults a where a.customer_tin = searchresults.customer_tin and a.TIN_Type = searchresults.TIN_Type and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when ID_NUMBER is not null then (select sum(cash_in) from searchresults a where a.ID_NUMBER = searchresults.ID_NUMBER and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  else (select sum(cash_in) from searchresults a where a.SEARCH_NUM = searchresults.SEARCH_NUM)
  end as 'total_per_period_cash_in'
 ,case when account_FK is not null then (select sum(cash_out) from searchresults a where a.account_FK = searchresults.account_FK and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when customer_cif is not null then (select sum(cash_out) from searchresults a where a.customer_cif = searchresults.customer_cif and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when customer_tin is not null then (select sum(cash_out) from searchresults a where a.customer_tin = searchresults.customer_tin and a.TIN_Type = searchresults.TIN_Type and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  when ID_NUMBER is not null then (select sum(cash_out) from searchresults a where a.ID_NUMBER = searchresults.ID_NUMBER and a.SEARCH_NUM = searchresults.SEARCH_NUM)
  else (select sum(cash_out) from searchresults a where a.SEARCH_NUM = searchresults.SEARCH_NUM)
  end as 'total_per_period_cash_out'
  from SearchResults 

重頭部分是搜尋結果 cte 一遍又一遍地呼叫自己，執行計劃如下所示：

有沒有辦法通過獲得相同的結果集並避免繁重的執行計劃來避免這種情況？

我認為您可能在錯誤假設下編寫程式碼：CTE 結果是持久的

它們不是，每次您引用它們時，都會重新執行語法。

這是一個簡單的例子：

CREATE TABLE #dummy
(
   id INT
);
INSERT #dummy ( id )
VALUES ( 1 );


WITH yourmom
AS ( SELECT d.id
    FROM   #dummy AS d )
SELECT ym.id
FROM   yourmom AS ym
JOIN   yourmom AS ym2
ON ym2.id = ym.id
JOIN   yourmom AS ym3
ON ym3.id = ym.id;

如果您查看查詢計劃，則會對基表進行 3 次掃描——初始掃描FROM一次，每個掃描一次JOIN。一共是三個。

這在幾個地方引起了問題：

1. 您加入transactionsCTE
2. 你要transactions進去searchResults
3. COUNT您最終選擇的所有子查詢searchresults

這導致了您的第二個問題：CASE您用來計算日期FROM和TO日期的這兩個表達式完全是non-SARGable。

你有兩個選擇：

1. 將您的第一個 CTE 的結果粘貼到#temp table
2. 將計算列添加到基表

在這種情況下，我可能會選擇計算列

ALTER TABLE dbo.parmeters_table
ADD FROM_DATE_SEARCHED AS CASE WHEN FROM_DATE IS NOT NULL THEN FROM_DATE
                              WHEN FROM_DATE IS NULL
                                   AND SEARCH_DAYS IS NOT NULL
                                   AND TO_DATE IS NOT NULL THEN DATEADD(DAY, SEARCH_DAYS * -1, TO_DATE)
                              WHEN FROM_DATE IS NULL
                                   AND SEARCH_DAYS IS NOT NULL
                                   AND TO_DATE IS NULL THEN DATEADD(DAY, SEARCH_DAYS * -1, GETDATE())
                              WHEN FROM_DATE IS NULL
                                   AND SEARCH_DAYS IS NULL
                                   AND TO_DATE IS NOT NULL
                                   AND DURATION = 'Yearly' THEN DATEADD(YEAR, -1, TO_DATE)
                              WHEN FROM_DATE IS NULL
                                   AND SEARCH_DAYS IS NULL
                                   AND TO_DATE IS NULL
                                   AND DURATION = 'Yearly' THEN DATEADD(YEAR, -1, GETDATE())
                              WHEN FROM_DATE IS NULL
                                   AND SEARCH_DAYS IS NULL
                                   AND TO_DATE IS NOT NULL
                                   AND DURATION = 'Monthly' THEN DATEADD(MONTH, -1, TO_DATE)
                              WHEN FROM_DATE IS NULL
                                   AND SEARCH_DAYS IS NULL
                                   AND TO_DATE IS NULL
                                   AND DURATION = 'Monthly' THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
                              WHEN FROM_DATE IS NULL
                                   AND SEARCH_DAYS IS NULL
                                   AND TO_DATE IS NOT NULL
                                   AND DURATION = 'Weekly' THEN DATEADD(DAY, -7, TO_DATE)
                              WHEN FROM_DATE IS NULL
                                   AND SEARCH_DAYS IS NULL
                                   AND TO_DATE IS NULL
                                   AND DURATION = 'Weekly' THEN DATEADD(DAY, -7, GETDATE())
                              ELSE DATEADD(MONTH, -1, GETDATE())
                         END,
   TO_DATE_SEARCHED AS CASE WHEN TO_DATE IS NOT NULL THEN TO_DATE
                            WHEN TO_DATE IS NULL
                                 AND DURATION = 'Monthly' THEN DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1)
                            ELSE GETDATE()
                       END;

您可以索引這些列以使您的聯接dbo.ixf_transaction更有效，並且避免必須預處理所有數據然後加入它。

我傾向於使用臨時表來保存searchResults. CASE這防止了對每個表達式重新執行的需要。

希望這可以幫助！

有沒有辦法通過獲得相同的結果集並避免繁重的執行計劃來避免這種情況？

嘗試將 cte searchresult 的結果保存到臨時表中，並在主查詢中使用臨時表

引用自：https://dba.stackexchange.com/questions/194278