性能提升外部應用
我有一個執行速度稍慢的查詢。
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
條件轉換為UNION
s。我將創建一個有限的範例,以顯示我所追求的一般查詢計劃形狀。我還將假設該
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
提示查詢在我的機器上七秒內完成。這是計劃,這是螢幕截圖: