性能提升外部應用

我有一個執行速度稍慢的查詢。

SELECT b.BreakdownClassificationId,
      k.IsinCode,
      k.SedolCode,
      ClassificationDate,
      NAME,
      InstrumentType,
      GeographicalLocation,
      CapSize,
      Currency,
      ExchangeName,
      HoldingDomicile,
      MaturityDate,
      Sector,
      MajorSector
FROM   #BreakdownSet b
      OUTER apply (SELECT TOP 1 IsinCode,
                                SedolCode,
                                ClassificationDate,
                                NAME,
                                InstrumentType,
                                GeographicalLocation,
                                CapSize CapSize,
                                Currency,
                                ExchangeName,
                                HoldingDomicile,
                                MaturityDate,
                                Sector,
                                MajorSector
                   FROM   dbfinex.dbo.PfPortfolioHoldingClassificationFtid x WITH (nolock)
                   WHERE  ( x.isincode > ''
                            AND x.isincode = b.breakdowncode )
                           OR ( x.sedolcode > ''
                                AND x.sedolcode = b.breakdowncode )
                           OR ( x.sedolcode > ''
                                AND x.sedolcode = b.sedolcode )
                           OR ( x.isincode > ''
                                AND x.isincode = b.isincode )
                   ORDER  BY CASE
                               WHEN x.sedolcode = b.breakdowncode THEN 1
                               WHEN x.isincode = b.breakdowncode THEN 2
                               WHEN x.sedolcode = b.sedolcode THEN 3
                               WHEN x.isincode = b.isincode THEN 4
                               ELSE 5
                             END,
                             classificationdate DESC) k 

執行計劃

Order By裡面Cross Apply是非常昂貴的，有沒有更好的方法來編寫這個查詢？

如果您願意，您可以取消排序，儘管很難說這是否有必要提高查詢性能。關鍵是您如何構造ORDER BY子句以及搜尋條件。如果有任何匹配的行，x.sedolcode = b.breakdowncode那麼您想要獲取該行，否則您轉到下一個條件。使用正確的索引，我們可以通過拆分APPLY. SQL Server 查詢優化器實際上提供了一個很好的提示，因為它將您的OR條件轉換為UNIONs。

我將創建一個有限的範例，以顯示我所追求的一般查詢計劃形狀。我還將假設該PfPortfolioHoldingClassificationFtid表在列上有一個主鍵和一個集群鍵PK。這是我的測試數據：

CREATE TABLE #BreakdownSet (
   BreakdownClassificationId BIGINT NOT NULL,
   breakdowncode VARCHAR(10) NULL,
   sedolcode VARCHAR(10) NULL,
   isincode VARCHAR(10) NULL
);

INSERT INTO #BreakdownSet
SELECT 
 t.RN
, CASE WHEN RN % 10 = 1 THEN t.RN ELSE NULL END
, CASE WHEN RN % 10 = 4 THEN t.RN ELSE NULL END
, CASE WHEN RN % 10 = 7 THEN t.RN ELSE NULL END
FROM
(
   SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
   FROM master..spt_values t1
   CROSS JOIN master..spt_values t2
) t;

CREATE TABLE dbo.PfPortfolioHoldingClassificationFtid (
   PK BIGINT NOT NULL,
   isincode VARCHAR(10) NOT NULL,
   sedolcode VARCHAR(10) NOT NULL,
   ClassificationDate DATE NOT NULL,
   OTHER_COLUMN VARCHAR(200) NOT NULL,
   PRIMARY KEY (PK)
);

INSERT INTO dbo.PfPortfolioHoldingClassificationFtid WITH (TABLOCK)
SELECT 
 t.RN
, t.RN
, t.RN
, DATEADD(DAY, t.rn / 100, '20170101')
, REPLICATE('OTHER', 40)
FROM
(
   SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
   FROM master..spt_values t1
   CROSS JOIN master..spt_values t2
) t;

CREATE INDEX isin_date ON dbo.PfPortfolioHoldingClassificationFtid (isincode, ClassificationDate);

CREATE INDEX sedol_date ON dbo.PfPortfolioHoldingClassificationFtid (sedolcode, ClassificationDate);

這是您對我的表定義的查詢：

SELECT b.breakdownclassificationid, 
      k.isincode, 
      k.sedolcode, 
      classificationdate, 
      other_column 
FROM   #breakdownset b 
      OUTER apply (SELECT TOP 1 isincode, 
                                sedolcode, 
                                classificationdate, 
                                other_column 
                   FROM   dbo.pfportfolioholdingclassificationftid x WITH ( 
                          nolock) 
                   WHERE  ( x.isincode > '' 
                            AND x.isincode = b.breakdowncode ) 
                           OR ( x.sedolcode > '' 
                                AND x.sedolcode = b.breakdowncode ) 
                           OR ( x.sedolcode > '' 
                                AND x.sedolcode = b.sedolcode ) 
                           OR ( x.isincode > '' 
                                AND x.isincode = b.isincode ) 
                   ORDER  BY CASE 
                               WHEN x.sedolcode = b.breakdowncode THEN 1 
                               WHEN x.isincode = b.breakdowncode THEN 2 
                               WHEN x.sedolcode = b.sedolcode THEN 3 
                               WHEN x.isincode = b.isincode THEN 4 
                               ELSE 5 
                             END, 
                             classificationdate DESC) k; 

毫不奇怪，我得到了一個與你不同的計劃，但這種類型的估計成本仍然很高。如果我們將其APPLY分成四個部分，並且每個部分APPLY只返回表的主鍵呢？如果我們每個都有一個覆蓋索引，APPLY那麼我們可以通過最多四個索引查找找到匹配行的主鍵。不需要排序。我們也可以通過在 s 中添加過濾器來跳過不需要的搜尋，APPLY但這並不能保證行為。這是編寫它的一種方法：

SELECT b.breakdownclassificationid, 
      k.isincode, 
      k.sedolcode, 
      classificationdate, 
      other_column 
FROM   #breakdownset b 
      OUTER apply (SELECT TOP 1 pk 
                   FROM   dbo.pfportfolioholdingclassificationftid x 
                   WHERE  x.sedolcode = b.breakdowncode 
                   ORDER  BY classificationdate DESC) a1 
      OUTER apply (SELECT TOP 1 pk 
                   FROM   dbo.pfportfolioholdingclassificationftid x 
                   WHERE  x.isincode = b.breakdowncode 
                          AND a1.pk IS NOT NULL 
                   ORDER  BY classificationdate DESC) a2 
      OUTER apply (SELECT TOP 1 pk 
                   FROM   dbo.pfportfolioholdingclassificationftid x 
                   WHERE  x.sedolcode = b.sedolcode 
                          AND a2.pk IS NOT NULL 
                   ORDER  BY classificationdate DESC) a3 
      OUTER apply (SELECT TOP 1 pk 
                   FROM   dbo.pfportfolioholdingclassificationftid x 
                   WHERE  x.isincode = b.isincode 
                          AND a3.pk IS NOT NULL 
                   ORDER  BY classificationdate DESC) a4 
      LEFT OUTER JOIN dbo.pfportfolioholdingclassificationftid k 
                   ON k.pk = COALESCE(a1.pk, a2.pk, a3.pk, a4.pk);

臨時表中有 150 萬行，最壞的情況應該是 600 萬次非聚集索引查找和 150 萬次聚集索引查找。

查詢在我的機器上執行兩秒鐘。執行時無關緊要，因為我有無意義的數據。但是，計劃中沒有排序。我將查詢計劃上傳到Paste The Plan。您應該考慮為將來的問題也這樣做。這也是實際計劃的螢幕截圖：

您擁有的查詢執行嵌套循環連接並且不會並行。使用LOOP JOIN並MAXDOP 1提示查詢在我的機器上七秒內完成。這是計劃，這是螢幕截圖：

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