長時間執行的查詢 - 查詢優化

我在大約 30,000 條記錄上執行以下語法。該語法每天執行 4 到 5 次，執行時間在 5 到 10 分鐘之間。下面是帶有數據子集（不是 30,000 條記錄）的範例 DDL，如果有人告訴我如何上傳我的 XML，我可以上傳我的 XML，以便您查看查詢執行情況。

如果有人看到優化方法以減少返回數據的冗長時間，我將不勝感激。

Declare @NumOfLeads Table
(
   clientname varchar(500)
   ,numoflicensesrequested int
   ,calldate date
)
Declare @PurchasedLicenses Table
(
   clientname varchar(500)
   ,numoflicensespurchased int
   ,purchasedate date
)
Insert Into @NumOfLeads (clientname, numoflicensesrequested, calldate) Values
('Client A', 10, '2017-01-01'), ('Client B', 5, '2017-01-02')
,('Client C', 7, '2017-01-01'), ('Client D', 12, '2017-01-03')
Insert Into @PurchasedLicenses (clientname, numoflicensespurchased, purchasedate) Values
('Client A', 10, '2017-01-10'), ('Client C', 5, '2017-01-15'), ('Client E', 10, '2017-01-15')
,('Client F', 11, '2017-01-15'), ('Client G', 5, '2017-01-22')

Select clientname, numoflicensesrequested
INTO ProdData
FROM @NumOfLeads
WHERE calldate BETWEEN '2017-01-01' AND '2017-01-31'

ALTER TABLE ProdData
Add numlicensespurchased int NOT NULL DEFAULT(0)

MERGE INTO ProdData pd
USING (
   SELECT clientname, numoflicensespurchased as CNT
   FROM @PurchasedLicenses 
   WHERE purchasedate BETWEEN '2017-01-01' AND '2017-01-31'
) pl
   ON pd.clientname = pl.clientname
WHEN MATCHED THEN
   UPDATE SET pd.numlicensespurchased = pl.CNT
WHEN NOT MATCHED BY TARGET THEN
 INSERT (clientname, numlicensespurchased, numoflicensesrequested)
 VALUES (pl.clientname, pl.CNT, '0');

Select * FROM ProdData order by clientname asc

編輯

SQL XML 計劃在這裡 https://www.brentozar.com/pastetheplan/?id=SJ16cKXkZ

對於您的最終結果集，您希望 in 和 的每個不同值clientname都有@NumOfLeads一行@PurchasedLicenses。如果客戶端在兩個表中，則填充numoflicensesrequested和numlicensespurchased列。如果沒有，則填充您擁有的一列。對我來說，業務邏輯聽起來很簡單，您不需要將其拆分為多個查詢。樣本數據：

CREATE TABLE #NumOfLeads
(
 clientname varchar(500)
, numoflicensesrequested int
, calldate date
, PRIMARY KEY (clientname)
);

CREATE TABLE #PurchasedLicenses
(
 clientname varchar(500)
, numoflicensespurchased int
, purchasedate date
, PRIMARY KEY (clientname)
)

Insert Into #NumOfLeads (clientname, numoflicensesrequested, calldate) Values
('Client A', 10, '2017-01-01'), ('Client B', 5, '2017-01-02')
,('Client C', 7, '2017-01-01'), ('Client D', 12, '2017-01-03');

Insert Into #PurchasedLicenses (clientname, numoflicensespurchased, purchasedate) Values
('Client A', 10, '2017-01-10'), ('Client C', 5, '2017-01-15'), ('Client E', 10, '2017-01-15')
,('Client F', 11, '2017-01-15'), ('Client G', 5, '2017-01-22');

此查詢返回與您相同的結果：

SELECT
 nol.clientname
, nol.numoflicensesrequested
, COALESCE(pl.numoflicensespurchased, 0) AS numoflicensespurchased
FROM #NumOfLeads nol
LEFT OUTER JOIN #PurchasedLicenses pl ON nol.clientname = pl.clientname
   AND pl.purchasedate BETWEEN '2017-01-01' AND '2017-01-31'
WHERE nol.calldate BETWEEN '2017-01-01' AND '2017-01-31'

UNION ALL

SELECT 
 pl.clientname
, 0
, pl.numoflicensespurchased
FROM #PurchasedLicenses pl
WHERE pl.purchasedate BETWEEN '2017-01-01' AND '2017-01-31'
AND NOT EXISTS (
   SELECT 1
   FROM #NumOfLeads nol
   WHERE nol.clientname = pl.clientname
)
order by clientname asc;

我相信查詢也可以用 a 編寫FULL OUTER JOIN，但我個人並不喜歡這種語法。

有幾點值得一提：

不要使用表變數，除非您需要它們提供的功能。表變數沒有統計資訊，因此如果您以錯誤的方式使用它們，它們可能會導致查詢性能下降。就個人而言，如果我需要通過事務回滾來持久化數據，或者如果我有每秒執行數千次（或更多）的程式碼，我只會考慮表變數。

您更新的事實clientname意味著它是數據的主鍵。為什麼不將其定義為主鍵？

對於復雜的查詢，您可以通過將中間結果保存到臨時表來將它們分成多個步驟來查看性能提升。如果我無法通過單個查詢獲得足夠好的性能，我只會嘗試這樣做。對於這個數據和業務邏輯，我認為你可以只寫一個SELECT查詢。對於其他過於復雜的查詢，請嘗試插入臨時表。這將為您提供最少的日誌記錄和其他好處。避免進行更新（如果可行），並避免使用 MERGE，除非你真的需要它的功能。

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