優化表值函式 SQL Server

January 2, 2019

我正在嘗試優化這個表值函式。如果可以，我會將其更改為程序，但我不能。問題在於兩個更新語句。我只在函式中保留了這兩個，因為它們會導致主要的性能問題。我將第一個從外部應用重寫到內部連接，我查看了統計數據，它們是錯誤的，所以我添加了一個選項（重新編譯），它有很大幫助。問題出在第二次更新中。統計數據是錯誤的，我不知道如何制定適當的執行計劃並通過提示進行優化。你知道如何減少時間嗎？我試圖索引表變數但沒有結果。

這是一個執行計劃https://www.brentozar.com/pastetheplan/?id=B1EdBo5e4

謝謝。

CREATE FUNCTION [dbo].[cfn_PlanServis_Seznam](
  @IDVazRole INT,
  @IDUzivatel INT,
  @IDRole INT,
  @IDLokalita INT,
  @lCid INT
)

RETURNS @PlanServis TABLE(
  lIDAuto INT,
  szSPZ VARCHAR(100),
  lDepozit INT,
  szTypVozidla varchar(100),
  szTypServisu NVARCHAR(300),
  szServisniPlan NVARCHAR(300),
  lZbyvaDni INT,
  lZbyvaKm INT,
  lNajetoKm INT,
  dtServis DATETIME,
  dcZbyvaMotohodin DECIMAL(15,1),
  dcNajetoMotohodin DECIMAL(15,1),
  IDVazPlanServisAuto INT,
  IDPlanServisDefinice INT,
  lBarva INT

)

AS

BEGIN
   DECLARE @Auto TABLE(
      lIDAuto INT,
      szSPZ VARCHAR(100),
      szTyp VARCHAR(100),
      IDCisTypServis INT,
      szTypServisu NVARCHAR(500),
      szServisniPlan NVARCHAR(500),
      lKmStart INT,
      dtStart DATETIME,
      lKmPriZavedeni INT,
      lUjetoPredZavedenim INT,
      dcMotohodinyStart DECIMAL(15,1),
      lIntervalKm INT,
      dcIntervalMotohodiny DECIMAL(15,1),
      lUjeto INT,
      dcMotohodiny DECIMAL(15,1),
      IDServis INT,
      lKmServis INT,
      dcMotohodinyServis DECIMAL(15,1),
      dtServis DATETIME,
      lIntervalDatum INT,
      lDniUbehlo INT,
      lBarva INT,
      lZbyvaKm INT,
      dcZbyvaMotohodin DECIMAL(15,2),
      lZbyvaDni INT,
      lDepozit INT,
      IDVazPlanServisAuto INT,
      IDPlanServisDefinice INT,
      lMaxTachograf INT,
      lKmPretaceni INT,
      dtOd DATE,
      lKmPosledniServis INT
   )

   DECLARE @IDCisAutoParametrKmPriZavadeni INT = 10012
   DECLARE @lKmPred INT = 30000
   DECLARE @lKmPredMensi INT = 15000
   DECLARE @lDniPred INT = 60
   DECLARE @lDniPredMensi INT = 30
   DECLARE @lMotohodinyPred INT = 100
   DECLARE @lMotohodinyPredMensi INT = 50
   DECLARE @IDBarvaBlizi INT = 1010 --Odkaz do CisTermBarva
   DECLARE @IDBarvaBliziMensi INT = 1016 --Odkaz do CisTermBarva
   DECLARE @IDBarvaPres INT  = 1017 --Odkaz do CisTermBarva
   --============ Koenc deklarace promennych ===========

   INSERT INTO @Auto (lIDAuto, szSPZ, szTyp, IDCisTypServis, szTypServisu, lKmStart, dtStart, lKmPriZavedeni, dcMotohodinyStart,[@Auto].lIntervalKm,[@Auto].dcIntervalMotohodiny,[@Auto].lIntervalDatum,szServisniPlan,[@Auto].IDVazPlanServisAuto,[@Auto].IDPlanServisDefinice)
   SELECT Auto.lIDAuto,
          Auto.szSpz,
          CASE WHEN Auto.lTyp = 0 THEN 'Taha?'  WHEN Auto.lTyp =  1 THEN 'N?v?s' ELSE '' END,
          PlanServisDefinice.IDCisTypServis,
          dbo.GetLocalText('CisTypServis','szNazev',CisTypServis.lIDCisTypServis,@lCid,CisTypServis.szNazev,''),
          PlanServisDefinice.lStartKM,
          PlanServisDefinice.dtStartDatum,
          CONVERT(INT,VazAutoParametr.varHodnota),
          PlanServisDefinice.dcMotohodinyStart,
          PlanServisDefinice.lIntervalKM,
          PlanServisDefinice.dcIntervalMotohodin,
          PlanServisDefinice.lIntervalDatum,
          PlanServis.szNazev,
          PlanServisDefinice.IDVazPlanServisAuto,
          PlanServisDefinice.lIDPlanServisDefinice
   FROM Auto INNER JOIN VazPlanServisAuto ON Auto.lIDAuto = VazPlanServisAuto.IDAuto
            INNER JOIN PlanServisDefinice ON VazPlanServisAuto.lIDVazPlanServisAuto = PlanServisDefinice.IDVazPlanServisAuto   
            INNER JOIN CisTypServis ON PlanServisDefinice.IDCisTypServis = CisTypServis.lIDCisTypServis
            LEFT OUTER JOIN VazAutoParametr ON VazAutoParametr.IDAuto = Auto.lIDAuto AND VazAutoParametr.IDCisAutoParametr = @IDCisAutoParametrKmPriZavadeni
            INNER JOIN PlanServis ON VazPlanServisAuto.IDPlanServisu = PlanServis.lIDPlanServis


   UPDATE @Auto SET lUjeto = Km.lKm
       FROM @Auto INNER JOIN 
       (SELECT
                   SUM(JizdaTachograf.lkmDo - JizdaTachograf.lkmOd) AS lKm, JizdaTachograf.IDAuto,JizdaTachograf.IDNaves
       FROM Jizda
           INNER JOIN JizdaTachograf ON JizdaTachograf.IDJizda = Jizda.lIDJizda
       WHERE JizdaTachograf.lkmOd IS NOT NULL
           AND JizdaTachograf.lkmDo IS NOT NULL
           AND Jizda.lProvozne = 1
       GROUP BY 
           JizdaTachograf.IDAuto,JizdaTachograf.IDNaves    
       ) as Km 
           ON Km.IDAuto = [@Auto].lIDAuto OR Km.IDNaves = [@Auto].lIDAuto
           OPTION  (RECOMPILE)


   UPDATE @Auto SET lMaxTachograf = ISNULL(ISNULL(Km.lKm, [@Auto].lKmServis),[@Auto].lKmStart)
   FROM @Auto 
        OUTER APPLY 
   (SELECT TOP 1 JizdaTachograf.lkmDo lKm 
    FROM Jizda INNER JOIN 
    JizdaTachograf ON JizdaTachograf.IDJizda = Jizda.lIDJizda 
    WHERE JizdaTachograf.lkmOd IS NOT NULL AND 
          JizdaTachograf.lkmDo IS NOT NULL AND Jizda.lProvozne = 1 
          AND (JizdaTachograf.IDAuto = [@Auto].lIDAuto 
          OR JizdaTachograf.IDNaves = [@Auto].lIDAuto) 
          AND Jizda.dtZacatek &gt; ISNULL(ISNULL([@Auto].dtServis, 
          [@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
    ORDER BY 
          Jizda.dtZacatek DESC, JizdaTachograf.lkmDo desc) Km



   INSERT INTO @PlanServis (lIDAuto, 
                            szSPZ, 
                            lDepozit, 
                            szTypVozidla, 
                            szTypServisu, 
                            szServisniPlan, 
                            lZbyvaDni, 
                            lZbyvaKm, 
                            lNajetoKm, 
                            dtServis, 
                            dcZbyvaMotohodin, 
                            dcNajetoMotohodin, 
                            IDVazPlanServisAuto, 
                            IDPlanServisDefinice,
                            lBarva)
   SELECT lIDAuto, 
           szSPZ, 
           lDepozit, 
           szTyp, 
           szTypServisu, 
           szServisniPlan, 
           lZbyvaDni, 
           lZbyvaKm, 
           lUjeto,--lNajetoKm, 
           dtServis, 
           dcZbyvaMotohodin, 
           dcMotohodiny,--dcNajetoMotohodin, 
           IDVazPlanServisAuto, 
           IDPlanServisDefinice,
           lBarva
     FROM @Auto 
   RETURN
END
GO

對於這樣的問題，提供MCVE非常有幫助。因為我不得不對錶結構和數據分佈做出很多猜測。您說這部分查詢計劃太慢了，沒有任何進一步的闡述：
我可以看到這部分可能很慢的三個原因。第一個問題是兩個表總共只有 176k 行，但索引查找從兩個表中提取了超過 800k 行。第二個問題是 JizdaTachograf 上的索引搜尋只有以下搜尋謂詞：[Lori_MDL].[dbo].[JizdaTachograf].lkmOd IS NOT NULL. 我想這可能是有選擇性的，但如果不是，那麼您實際上是在掃描大多數索引 845 表。第三個問題是總共對 800k 行進行了排序，儘管這些排序分為 846 次迭代。
可能有一種方法可以製定一個只對兩個表進行一次掃描的計劃，但是在不了解數據分佈的情況下，我不知道這是否值得。您的查詢要求（不等式、排序、或邏輯）使合併連接或雜湊連接難以工作。
您可以解決的第一個問題是第二個。如果您定義正確的索引並將其拆分為兩個子查詢，那麼您可以在該搜尋上直接對相關行進行(JizdaTachograf.IDAuto = [@Auto].lIDAuto OR JizdaTachograf.IDNaves = [@Auto].lIDAuto)更有效的索引搜尋。JizdaTachograf如果表中的大多數行都具有非 NULL 值，則可以節省大量時間lkmOd。有許多不同的索引定義可以工作。下面是兩個：
CREATE INDEX IX2 ON JizdaTachograf (IDAuto, IDJizda, lkmDo) INCLUDE (lkmOd)
WHERE lkmOd IS NOT NULL AND lkmDo IS NOT NULL;

CREATE INDEX IX3 ON JizdaTachograf (IDNaves, IDJizda, lkmDo) INCLUDE (lkmOd)
WHERE lkmOd IS NOT NULL AND lkmDo IS NOT NULL;
然後我拆分查詢，以便 SQL Server 可以利用索引。
UPDATE @Auto SET lMaxTachograf = ISNULL(ISNULL(Km.lKm, [@Auto].lKmServis),[@Auto].lKmStart)
   FROM @Auto 
   OUTER APPLY 
   (
   SELECT TOP (1) lKm
   FROM
   (

   SELECT TOP (1) Jizda.dtZacatek, JizdaTachograf.lkmDo lKm 
    FROM JizdaTachograf
    INNER JOIN Jizda WITH (INDEX(1)) ON JizdaTachograf.IDJizda = Jizda.lIDJizda 
    WHERE JizdaTachograf.lkmOd IS NOT NULL AND 
          JizdaTachograf.lkmDo IS NOT NULL AND Jizda.lProvozne = 1 
          AND JizdaTachograf.IDAuto = [@Auto].lIDAuto -- first half
          AND Jizda.dtZacatek &gt; ISNULL(ISNULL([@Auto].dtServis, [@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
    ORDER BY  Jizda.dtZacatek DESC, JizdaTachograf.lkmDo desc

    UNION ALL

    SELECT TOP (1) Jizda.dtZacatek, JizdaTachograf.lkmDo lKm 
    FROM JizdaTachograf
    INNER JOIN Jizda WITH (INDEX(1)) ON JizdaTachograf.IDJizda = Jizda.lIDJizda 
    WHERE JizdaTachograf.lkmOd IS NOT NULL AND 
          JizdaTachograf.lkmDo IS NOT NULL AND Jizda.lProvozne = 1 
          AND JizdaTachograf.IDNaves = [@Auto].lIDAuto -- second half
          AND Jizda.dtZacatek &gt; ISNULL(ISNULL([@Auto].dtServis, [@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
    ORDER BY Jizda.dtZacatek DESC, JizdaTachograf.lkmDo desc
   ) IDNaves_IDAuto
   ORDER BY dtZacatek DESC, lKm DESC   
   ) Km;
我正在使用空表，但我可以證明至少可以獲得所需的計劃形狀：
這個計劃的優點是它會做更少的 IOJizdaTachograf並且分類被進一步分割。但是，您仍然從兩個索引中提取相同數量的行並對相同的總行數進行排序。
可以編寫此查詢以便沒有排序。IO 模式不同，這可能導致整體讀取次數減少。您將需要另一個索引。下面是一個有效的：
CREATE INDEX IX1 ON Jizda (dtZacatek) INCLUDE (lIDJizda, lProvozne)
WHERE lProvozne = 1;
優化器不能總是做出與排序數據相同的推斷，因此我更改了查詢以使其了解不需要排序：
UPDATE @Auto SET lMaxTachograf = ISNULL(ISNULL(Km.lKm, [@Auto].lKmServis),[@Auto].lKmStart)
   FROM @Auto 
   OUTER APPLY 
   (
   SELECT TOP (1) lkmDo lKm
   FROM
   (
       SELECT TOP (1) Jizda.dtZacatek, ca.lkmDo
       FROM Jizda 
       CROSS APPLY (
            SELECT TOP (1) JizdaTachograf.lkmDo
            FROM JizdaTachograf
            WHERE JizdaTachograf.IDJizda = Jizda.lIDJizda 
            AND JizdaTachograf.lkmOd IS NOT NULL AND 
            JizdaTachograf.lkmDo IS NOT NULL
            AND JizdaTachograf.IDNaves = [@Auto].lIDAuto  -- this line is different    
            ORDER BY JizdaTachograf.lkmDo DESC          
       ) ca
       WHERE Jizda.lProvozne = 1 
       AND Jizda.dtZacatek &gt; ISNULL(ISNULL([@Auto].dtServis,[@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
       ORDER BY Jizda.dtZacatek DESC

       UNION ALL

       SELECT TOP (1) Jizda.dtZacatek, ca.lkmDo
       FROM Jizda 
       CROSS APPLY (
            SELECT TOP (1) JizdaTachograf.lkmDo
            FROM JizdaTachograf
            WHERE JizdaTachograf.IDJizda = Jizda.lIDJizda 
            AND JizdaTachograf.lkmOd IS NOT NULL AND 
            JizdaTachograf.lkmDo IS NOT NULL
            AND JizdaTachograf.IDAuto = [@Auto].lIDAuto -- this line is different  
            ORDER BY JizdaTachograf.lkmDo DESC          
       ) ca
       WHERE Jizda.lProvozne = 1 
       AND Jizda.dtZacatek &gt; ISNULL(ISNULL([@Auto].dtServis,[@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
       ORDER BY Jizda.dtZacatek DESC
   )  IDNaves_IDAuto

   ORDER BY dtZacatek DESC, lkmDo DESC
   ) Km
現在沒有任何排序：
然而，這是一個有點危險的優化。現在Jizda是嵌套循環連接的外部表。考慮在@AutoNULL 為[@Auto].dtServisNULL的行[@Auto].dtStart，並且沒有與JizdaTachografbyIDNaves和的匹配IDAuto。SQL Server 將讀取所有 180k 行Jizda並執行 180k 索引搜尋JizdaTachograf以最終不返回任何行。我不知道這種情況發生的可能性有多大，但它可能會發生。
根據問題中提供的資訊，我的建議是嘗試第一個查詢，看看它是否足夠快。如果沒有，請使用過濾器實現這兩個查詢。掃描一個 845 行的表變數完全不需要時間，因此您可以使用兩個單獨的 UPDATE` 語句在表的不同部分進行操作，從而充分利用這兩個查詢。當沒有非 NULL 日期列時，第一個查詢可能更有效：
WHERE [@Auto].dtServis IS NULL AND [@Auto].dtStart IS NULL;
當存在非 NULL 日期列時，第二個查詢可能更有效（我假設該列具有一定的選擇性）：
WHERE [@Auto].dtServis IS NOT NULL OR [@Auto].dtStart IS NOT NULL

我認為正在更新@Auto 表的查詢的一部分可以進一步插入到表變數中。

這樣，同樣的查詢不會重複。所以它會提高性能。

請理解這個想法並在需要的地方做正確的事情。

declare @table TABLE(lkmDo int, lkmOd int,IDAuto int, IDNaves int,dtZacatek datetime )
insert into @table
SELECT JizdaTachograf.lkmDo ,JizdaTachograf.lkmOd ,IDAuto,IDNaves,dtZacatek
    FROM Jizda INNER JOIN 
    JizdaTachograf ON JizdaTachograf.IDJizda = Jizda.lIDJizda 
    WHERE JizdaTachograf.lkmOd IS NOT NULL AND 
          JizdaTachograf.lkmDo IS NOT NULL AND Jizda.lProvozne = 1 
          AND (JizdaTachograf.IDAuto = [@Auto].lIDAuto 
          OR JizdaTachograf.IDNaves = [@Auto].lIDAuto) 
-- OPTION  (RECOMPILE)

   UPDATE @Auto SET lUjeto = Km.lKm
       FROM @Auto INNER JOIN 
       (SELECT
                   SUM(lkmDo - lkmOd) AS lKm, IDAuto,IDNaves
       FROM @table
       GROUP BY 
           JizdaTachograf.IDAuto,JizdaTachograf.IDNaves    
       ) as Km 
           ON Km.IDAuto = [@Auto].lIDAuto OR Km.IDNaves = [@Auto].lIDAuto



   UPDATE @Auto SET lMaxTachograf = ISNULL(ISNULL(Km.lKm, [@Auto].lKmServis),[@Auto].lKmStart)
   FROM @Auto 
        OUTER APPLY 
   (SELECT TOP 1 JizdaTachograf.lkmDo lKm 
    FROM @table
    WHERE
          Jizda.dtZacatek &gt; ISNULL(ISNULL([@Auto].dtServis, 
          [@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
    ORDER BY 
          Jizda.dtZacatek DESC, JizdaTachograf.lkmDo desc) Km

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

優化表值函式 SQL Server

相關問答

SQL 連接的性能

TOP 如何（以及為什麼）影響執行計劃？

查詢計劃中有關記憶體“過度授予”的警告 - 如何找出導致它的原因？

非聚集主鍵和覆蓋索引在性能方面的比較

常量變數為空時執行計劃不同

查詢以基於“時間”列對行進行分類，而不使用 CASE 表達式