使用 CTE 時的優化

我想獲取每個受僱銷售人員的匯總數據並顯示Salesname、saleamount和saleshippingamount。我將查詢設置為使用 2 個UPDATECTE 語句來更新包含每個員工的主數據的表。我正在更新，因為我需要顯示總計。

該語法大約需要 10 分鐘才能完成，並且在兩個表之間大約有 60,000 行需要處理。CTE 是解決這個問題的錯誤方法嗎？

下面是範例 DDL。員工姓名是從 HR 表中填充的，但我沒有為此顯示 DDL，只是一個插入語句。

Create Table #FinalData
(
   employee varchar(400)
   ,tr float
   ,tf float
)
Create Table #TR
(
   employee varchar(400)
   ,saleamount float
   ,saledate date
)
Create Table #TF
(
   employee varchar(400)
   ,saleshippingamt float
   ,saledate date
)
INSERT INTO #TR (employee, saleamount, saledate) VALUES
('Employee 1', '12.63', '2017-01-01'), ('Employee 1', '15.00' ,'2017-01-02')
,('Employee 2', '14.00', '2017-01-03'), ('Employee 2' ,'12.00', '2017-01-03')
,('Employee 3', '16.00', '2017-01-03'), ('Employee 3', '13.00', '2017-01-04')
INSERT INTO #TF (employee, saleshippingamt, saledate) VALUES
('Employee 1', '5.00', '2017-01-01'), ('Employee 1', '6.00' ,'2017-01-02')
,('Employee 2', '5.50', '2017-01-03'), ('Employee 2' ,'5.00', '2017-01-03')
,('Employee 3', '6.00', '2017-01-03'), ('Employee 3', '6.00', '2017-01-04')
INSERT INTO #FINALDATA (employee) Values
('Employee 1'), ('Employee 2'), ('Employee 3')

;With TR As 
(
   SELECT 
   employee
   ,SUM(saleamount) Totals
   FROM #TR
   WHERE saledate BETWEEN '2017-01-01' AND '2017-01-25'
   GROUP BY employee
)
UPDATE t
SET tr = t2.TotalCount
FROM #FinalData t
INNER JOIN (Select employee, SUM(Totals) TotalCount
           FROM TR
           GROUP BY employee) As t2
ON t.employee = t2.employee

;With TF As 
(
   SELECT 
   employee
   ,SUM(saleshippingamt) Totals
   FROM #TF
   WHERE saledate BETWEEN '2017-01-01' AND '2017-01-25'
   GROUP BY employee
)
UPDATE t
SET tf = t2.TotalCount
FROM #FinalData t
INNER JOIN (Select employee, SUM(Totals) TotalCount
           FROM TF
           GROUP BY employee) As t2
ON t.employee = t2.employee

Select * FROM #FinalData

這是我的執行計劃。

我必須優化此查詢的選項是什麼？

我終於能夠進行一些測試和使用查詢，並可以給你更具體的建議。我使用 AdventureWorks 作為我的範例，所以我有一些實際數據可以使用。

選項 1 - 添加索引

這將以最少的努力為您提供最佳的性能提升。僅通過添加三個索引，我就看到我的樣本集中的查詢執行增加了 40%。

表#TR和#TF. 在 ,上添加索引saledate，employee並包括金額。

CREATE INDEX IDXNC_TFSaleDateEmployee ON #TF (saledate, employee) INCLUDE (saleshippingamt)

CREATE INDEX IDXNC_TRSaleDateEmployee ON #TR (saledate, employee) INCLUDE (saleamount)

對於#FinalData，您應該在該employee欄位上有一個聚集索引。

CREATE CLUSTERED INDEX IDXC_FinalDataEmployee ON #FinalData (employee)

選項 2 - 更改您的查詢

您的查詢可以使用一些調整，它甚至可以讓您一起擺脫臨時表。

此查詢的執行速度也稍快一些，但索引將為您提供最佳的性能提升。

;WITH CTE_Employee AS
   (
   SELECT DISTINCT employee
   FROM (
       SELECT employee
       FROM #TR
       UNION ALL
       SELECT employee
       FROM #TF
       ) AS D
   )
   , CTE_TR AS
   (
   SELECT employee
       , SUM(saleamount) AS TotalDue
   FROM #TR
   WHERE saledate BETWEEN '2017-01-01' AND '2017-01-25'
   GROUP BY employee
   )
   , CTE_TF AS
   (
   SELECT employee
       , SUM(saleamount) AS TotalDue
   FROM #TR
   WHERE saledate BETWEEN '2017-01-01' AND '2017-01-25'
   GROUP BY employee
   )

SELECT S.employee
   , TR.TotalDue
   , TF.TotalDue
FROM CTE_Employee AS S
   LEFT OUTER JOIN CTE_TR AS TR ON TR.employee = S.employee
   LEFT OUTER JOIN CTE_TF AS TF ON TF.employee = S.employee

普通的留言

您可以隨心所欲地命名 CTE，但如果您給它們起與它們所代表的表相同的名稱，它確實會使事情有些混亂。我喜歡給他們一個CTE_*前綴，但它是可選的。

您也可以像我在這裡所做的那樣連結 CTE。

在您的原始查詢中，當您totalcount獲取#FinalData. 這也是需要注意的事情，您可以看到它是不必要的，但是 SQL 會多次執行排序和求和操作。您可以只加入 CTE 表而不是進行子選擇。

你有過

UPDATE t
SET tf = t2.TotalCount
FROM #FinalData t
INNER JOIN (Select employee, SUM(Totals) TotalCount
           FROM TF
           GROUP BY employee) As t2
ON t.employee = t2.employee

應該

UPDATE t
SET tf = t2.TotalCount
FROM #FinalData t
INNER JOIN TF As t2 ON t.employee = t2.employee

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