使用 ORDER BY 時出現相同的查詢性能問題

我有使用一些 CTE 的查詢。最後的輸出查詢將 CTE 連接在一起。當我使用 no 執行查詢時ORDER BY，它會在幾秒鐘內執行。當我用 執行它時ORDER BY，需要幾分鐘才能返回。

我用大量記錄嘗試了相同的查詢。這一次，它跑了又跑（現在仍在執行），即使沒有ORDER BY.

我將 CTE 更改為臨時表，並且查詢執行得很快。

我不是 DBA，我想知道這種行為指向什麼類型的問題。我正在使用的這個查詢是要在應用程序中使用的儲存過程的一部分。在進行此切換之前，我應該考慮什麼？謝謝。

當您將 CTE 與另一個表連接時，SQL 可能（取決於執行計劃）多次執行 CTE 中的程式碼。

例如：針對 stackoverflow 數據庫執行：

set statistics io on;

with cte as (
select ID from Users where Location ='Canada'
)
select top 100 title from Posts p 
join cte on cte.Id=p.ParentId


select ID into #user from Users where Location ='Canada'
select top 100 title from Posts p 
join #user u on u.Id=p.ParentId
drop table #user

CTE 查詢對使用者表進行了大量讀取：

Table 'Users'. Scan count 0, logical reads 123369, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 9588, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

另一個帶有臨時表的查詢只從使用者那裡讀取一次數據，因此讀取的次數更少（導致查詢速度更快）

Table 'Users'. Scan count 1, logical reads 44573, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(3687 rows affected)

(1 row affected)

(100 rows affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 7183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#user_______________________________________________________________________________________________________________000000000045'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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