優化計算查詢

在遺留項目中，我有以下查詢：

SELECT 
   concat(SR_PO, '-', SR_RlsSequence) as Order,
   count(*) as Todo,
   sum(isnull(SR_Enabled,0)) as ToLoad,
   sum(isnull(SR_Done,0)) as Loaded,
   sum(isnull(SR_Enabled,0))-sum(isnull(SR_Done,0))-sum(isnull(Rejected,0)) as Missing,
   count(EndTime3) as Quot,
   sum(isnull(Rejected,0)) as Scrapped
FROM [Edgebanding]
WHERE concat(SR_PO, '-', SR_RlsSequence) IN ('100063-100', '100063-101', '100063-103', '100063-104')
GROUP BY by concat(SR_PO, '-', SR_RlsSequence)

但是它太貴了，尤其是當 IN 參數很多的時候。關於優化的任何想法？請考慮我不能使用儲存過程。

預計查詢計劃在此處上傳。

您可以嘗試應用過濾器

( WHERE concat(SR_PO, '-', SR_RlsSequence) IN ('100063-100', '100063-101', '100063-103', '100063-104'))

直接在索引上，而不是在執行計劃中進一步作為過濾器實現。

您可以通過不對子句中的列應用函式where並添加索引來做到這一點。group by 也更改為沒有該功能的工作。

查詢適配：

SELECT 
   concat(SR_PO, '-', SR_RlsSequence) as [Order],
   count(*) as Todo,
   sum(isnull(SR_Enabled,0)) as ToLoad,
   sum(isnull(SR_Done,0)) as Loaded,
   sum(isnull(SR_Enabled,0))-sum(isnull(SR_Done,0))-sum(isnull(Rejected,0)) as Missing,
   count(EndTime3) as Quot,
   sum(isnull(Rejected,0)) as Scrapped
FROM [Edgebanding]
WHERE SR_PO = '100063' AND SR_RlsSequence IN ('100','101','103','104')
GROUP BY SR_PO, SR_RlsSequence;

和索引：

CREATE INDEX IX_SR_PO_SR_RlsSequence
ON [dbo].[Edgebanding](SR_PO,SR_RlsSequence)
INCLUDE(SR_Enabled,SR_Done,Rejected,EndTime3);

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