Sql-Server

如何提高儲存過程的性能？

  • October 19, 2017

儲存過程

CREATE     procedure [dbo].[ImproveProcedure] (@port varchar(50), @portdate datetime)    
as    

Declare @intdate datetime    
select @intdate = max(rate_date) from Interestrate where rate_type = 'Zero'    
and rate_date <= @portdate    

Update transactiontable set NonDiscount = null, Discount = null, NonDiscountTcurr = null, DiscountTcurr = null,    
NonDiscountNew = null, DiscountNew = null    
where    
port = @port and portdate = @portdate    

Update tr set NonDiscount = (case sss_ind when 'P' then -exposure else exposure end) *    
     dbo.Foo(ccd, 'USD', @portdate,    
case when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) < 1 then 1     
when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) > 48 then 48     
else  datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature)))     
end),    
NonDiscountTcurr = (case sss_ind when 'P' then -exposure else exposure end)    
from    
Phy p1    
where    
port = @port and portdate = @portdate    
and    
tr.trans = p1.trans    
and    
p1.Sub <> 'Option'    


Update tr set NonDiscount = (case when buysell in ('A', 'S') then -vol * markprice else vol * markprice end) *    
     dbo.Foo(ccd, 'USD', @portdate,     
case when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) < 1 then 1     
when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) > 48 then 48     
else  datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature)))
end),    
NonDiscountTcurr = (case when buysell in ('A', 'S') then -vol * markprice else vol * markprice end)    
from    
Phy p1    
where    
port = @port and portfolio = @portfolio    
and    
tr.trans = p1.trans    
and    
p1.Sub = 'Option'





CREATE   function [dbo].[Foo]  
(@currency1 varchar(10),   
@currency2 varchar(10),  
@portdate datetime, @month int) returns float  

As  
BEGIN  
Declare  
@CurrentRate float,  
@Ratedate datetime  


select @Ratedate = max(rate_date) from fx where  
(  
currency1 = @currency1 and currency2 = @currency2 and forward_month = @month  
or  
currency2 = @currency1 and currency1 = @currency2 and forward_month = @month  
)  
and  
@portdate >= Rate_date  


IF exists ( select * from fx where currency1 = @currency1  
and currency2 = @currency2  and rate_date = @Ratedate and forward_month = @month)   


 SELECT @CurrentRate =  Rate from fx where currency1 = @currency1 and  
 currency2 = @currency2 and rate_date = @Ratedate and forward_month = @month  



ELSE   



 IF exists ( select * from fx where currency1 =  
 @currency2 and currency2 = @currency1 and rate_date = @Ratedate and forward_month = @month)  


 select @CurrentRate = 1/Rate  from fx  where currency1 = @currency2  
 and currency2 = @currency1 and rate_date = @Ratedate and forward_month = @month  


 ELSE  
  select @CurrentRate = 1   


return (@CurrRate )  
END

我知道標量函式呼叫會限制查詢的性能。有人可以看到一種刪除函式呼叫的方法，用JOINorCROSS APPLY或其他東西代替它們，看看這是否有助於提高性能？我試過了，但一直找不到方法。

有關目前性能的更多資訊：該過程的單次執行，更新約 25,000 行，需要 20-25 分鐘才能執行。查看執行計劃，我看到​​函式中綁定了很多讀取，並且我認為通過JOIN解決方案（或類似​​的解決方案），讀取次數會急劇下降，並且性能可能會提高。另外，如上所述，我聽說標量函式呼叫（通常）在查詢中“不好”。

這是將函式重寫為過程的嘗試：

CREATE PROCEDURE [dbo].[ImproveProcedure]
(
 @port varchar(50)
,@portdate datetime
)
AS   
BEGIN
   DECLARE @intdate datetime;
   SELECT @intdate = MAX(rate_date)
     FROM Interestrate
    WHERE rate_type = 'Zero'
      AND rate_date <= @portdate
   ;

   IF (OBJECT_ID('tempdb..#tmp_fx') IS NOT NULL) DROP TABLE #tmp_fx;
   CREATE TABLE #tmp_fx
        ( currency1 varchar(10)
         ,currency2 varchar(10)
         ,month int
         ,rate_date datetime
         ,rate float
         ,CONSTRAINT PK_tmp_fx PRIMARY KEY (currency2, month)
        );

   -- NOTE: currency1 not needed in PK, as it's always 'USD' - may want to change if add'l values are added.

   -- Get base currency pairs, months, and max dates
   INSERT INTO #tmp_fx
   SELECT currency1, currency2, forward_month, MAX(rate_date)
     FROM (SELECT currency1, currency2, forward_month, rate_date
             FROM fx
            WHERE currency1 = 'USD'
              AND rate_date <= @portdate
           UNION ALL
           SELECT currency2, currency1, forward_month, rate_date
             FROM fx
            WHERE currency2 = 'USD'
              AND rate_date <= @portdate
          ) sq
    GROUP BY currency1, currency2, forward_month
   ;

   -- Set rates based on previous query:
   --   Step 1: rates where currency1 = currency1 -- multiply
   UPDATE t
      SET rate = fx.Rate
     FROM #tmp_fx t
            INNER JOIN fx ON (    t.currency1 = fx.currency1
                              AND t.currency2 = fx.currency2
                              AND t.month = fx.forward_month
                              AND t.rate_date = fx.rate_date
                             )
   ;

   --   Step 2: rates where currency2 = currency1 -- divide
   UPDATE t
      SET rate = 1 / fx.Rate
     FROM #tmp_fx t
            INNER JOIN fx ON (    t.currency2 = fx.currency1
                              AND t.currency1 = fx.currency2
                              AND t.month = fx.forward_month
                              AND t.rate_date = fx.rate_date
                             )
    WHERE fx.rate IS NULL
   ;

   -- NOTE - unset rates left NULL - will force to 1 in final query


   UPDATE tr
      SET NonDiscount = null
         ,Discount = null
         ,NonDiscountTcurr = null
         ,DiscountTcurr = null
         ,NonDiscountNew = null
         ,DiscountNew = null    
    WHERE port = @port
      AND portdate = @portdate
   ;

   UPDATE tr
      SET NonDiscount =  CASE WHEN p1.Sub <> 'Option'
                           THEN CASE sss_ind
                                  WHEN 'P' then -exposure
                                  ELSE          exposure
                                END
                           ELSE CASE WHEN buysell IN ('A', 'S')
                                  THEN -vol * markprice
                                  ELSE vol * markprice
                                END
                         END
                       * ISNULL(fx.Rate, CAST(1 as float))
         ,NonDiscountTcurr = CASE WHEN p1.Sub <> 'Option'
                               THEN CASE sss_ind
                                      WHEN 'P' then -exposure
                                      ELSE          exposure
                                    END
                               ELSE CASE WHEN buysell IN ('A', 'S')
                                      THEN -vol * markprice
                                      ELSE vol * markprice
                                    END
                             END
     FROM tr
            INNER JOIN Phy p1 ON (tr.trans = p1.trans)
            LEFT  JOIN #tmp_fx fx ON (    'USD' = fx.currency1
                                      AND ccd = fx.currency2
                                      AND portdate = fx.rate_date
                                      AND  CASE 
                                             WHEN DATEDIFF(month, portdate, COALESCE(priceend1, priceend2, mature)) < 1
                                               THEN 1
                                             WHEN DATEDIFF(month, portdate, COALESCE(priceend1, priceend2, mature)) > 48
                                               THEN 48
                                             ELSE   DATEDIFF(month, portdate, COALESCE(priceend1, priceend2, mature))
                                           END
                                         = fx.month
                                     )
    WHERE port = @port
      AND portdate = @portdate
      AND p1.Sub IS NOT NULL
   ;
END;

您的函式使用ccd,和三個可能日期portdate之間的月數portdate（使用第一個不為 NULL 的日期）來計算exposure應乘以 的比率。

  • 如果你能找到匹配currency1的“USD”，那麼你乘以rate;
  • 如果唯一的匹配是currency2“USD”在哪裡，那麼除以rate;
  • 如果未找到匹配項，則rate預設為 1

因此，讓我們嘗試建立一個表格，其中包含您的可用費率。

我們基本上有兩個因素來確定rate_date我們將使用的值：

  • 月份，1 到 48 之間的整數；
  • 這兩種貨幣，其中一種是“美元”，另一種會有所不同。

這些中的每一個的最長日期將決定所需的費率。

實際上，這讓我們最多需要擔心 48 *（貨幣數量）行。我猜那是 10-15 千行，而不是一個不合理的大表。

出於加入目的，這將有助於使貨幣標準化，因此第一個始終是USD- 這樣我們就不必擔心我們是乘以還是除以匯率。

因此，我們建構了一個具有唯一值 、 和 的臨時表currency1（currency2始終forward_month將 ‘USD’ 放在臨時表的currency1列中），以及rate_date這三個值的每個唯一集的最大值。

然後，我們添加費率。如果它是相關的，我們會像你一樣做；如果我們遇到 ‘USD’ 在fxas中的情況currency1，我們會優先使用它而不是 ‘USD’ 在 中的任何行currency2。‘USD’ 在哪裡currrency1，我們rate直接儲存；它在哪裡currency2，我們需要除以比率，所以我們儲存1 / rate。

然後，我們不使用函式來查找速率，而是加入臨時表，基於：

  • 臨時表currency1是“美元”
  • 臨時表來自查詢currency2中ccd的其他表
  • rate_date匹配portdate列（與傳入的匹配@portdate）
  • 月份匹配portdate, 和 , 的第一個之間的月份數priceend1，priceend2並且mature不為 NULL。（注意：COALESCE獲取兩個或多個值的列表，並從列表中返回第一個非 NULL 值；它比 更容易閱讀ISNULL(x,ISNULL(y,z))，但工作方式相同）

我們將其設為 a LEFT JOIN，因此我們包含在臨時表中找不到匹配行的行。

然後，我們只需將適當的值（取決於 的值Sub）乘以返回的速率（或者乘以 1，如果我們返回 NULL，要麼是因為臨時表沒有匹配，要麼是因為臨時表有匹配的行一個 NULL rate）。

注意：我將您原來的兩個 UPDATE 語句合併為一個，CASE在p1.Sub. 為了保證這與您原來的兩個語句版本一樣工作，我還檢查了是否p1.Sub為NULL; 如果不能NULL，那麼您可以刪除該檢查。

試試這個，看看它是否適合你。它沒有完全優化（例如，我認為我們可以通過檢查目標交易行中實際存在的貨幣來減少臨時表中的行），但它確實刪除了您正在使用的函式的逐行性質。

注意：程式碼未經測試。

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

相關問答

Sql-Server

儲存過程與內聯 SQL

  • August 7, 2020
檢視問答
Sql-Server

@parameter IS NULL 可以減慢查詢的執行速度嗎？

  • June 5, 2020
檢視問答
Sql-Server

如何在子查詢 JOIN 條件中使用父表列？

  • September 22, 2019
檢視問答
Sql-Server

在更快的 CASE 語句中：“WHENF我___F一世和一世dfield在 (0)’ 或 ‘WHENF我___F一世和一世dfield= 0”？

  • May 5, 2019
檢視問答
Sql-Server

高效地執行多個相似的更新

  • May 5, 2019
檢視問答
Sql-Server

將列與值列表匹配的性能緩慢

  • January 14, 2019
檢視問答