WITH 语句是每个查询执行一次还是每行执行一次？

我对WITH语句（CTE）的理解是它每个查询执行一次。通过这样的查询：

WITH Query1 AS ( ... )
SELECT *
FROM
    SomeTable t1
    LEFT JOIN Query1 t2 ON ...

如果这导致 100 行，我预计Query1只被执行了一次——而不是 100 次。如果该假设正确，则运行整个查询所需的时间大致等于以下内容所需的时间：Query1+ 选择SomeTable + join SomeTable to Query1.

我现在的情况是：

• Query1单独运行大约需要 5 秒（400k 行）。
• 删除后查询的其余部分WITH声明和LEFT JOIN大约需要 15 秒（400k 行）。

因此，当使用以下命令运行整个查询时WITH声明和LEFT JOIN在适当的位置，我本希望查询能够及时完成，但我让它运行了一个多小时，一旦停止，它最多只能得到 11k 行。

我显然错了，但为什么呢？

Example:

SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS ON;

CREATE TABLE MyTable (MyID INT  PRIMARY KEY);
GO
INSERT  MyTable (MyID)
VALUES  (11), (22), (33), (44), (55);

PRINT 'Test MyCTE:';
WITH MyCTE
AS (
    SELECT  *, ROW_NUMBER()OVER(ORDER BY MyID) AS RowNum
    FROM    MyTable
)
SELECT  *
FROM    MyCTE crt
LEFT JOIN MyCTE prev ON crt.RowNum=prev.RowNum+1;

ROLLBACK;

If you run previous script in SSMS (press Ctrl+M -> Actual Execution Plan) then you will get this execution plan for the last query:

在这种情况下，CTE 执行一次crt别名和五（！）次prev别名，每行一次crt.

所以，这个问题的答案

WITH 语句是每个查询执行一次还是每行执行一次？

is both：每个查询一次（crt）并且每行一次（prev: 一次对于每个从crt).

为了优化这个查询，首先， 1）您可以尝试存储CTE的结果（MyCTE or Query) 到表变量或临时表中

2）定义该表的主键作为连接列，

3) 重写最终查询以使用此表变量或临时表。

当然，您可以尝试重写最终查询，而无需 CTE 之间的自连接。