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

Question

我对 WITH 语句 (CTE) 的理解是它对每个查询执行一次。使用这样的查询:

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

如果结果是 100 行，我预计 Query1 只执行了一次 - 而不是 100 次。如果这个假设是正确的，那么运行整个查询所花费的时间大致等于:运行 Query1 + select from SomeTable + join SomeTable 到 Query1。

我的处境是:

• Query1 单独运行需要大约 5 秒(40 万行)。
• 在删除 WITH 语句和 LEFT JOIN 之后，查询的其余部分大约需要 15 秒(40 万行)。

因此，当使用 WITH 语句和适当的 LEFT JOIN 运行整个查询时，我希望查询能够及时完成，但我让它运行了一个多小时，一旦停止它就只有 11000 行。

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

Answer 1

例子:

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;

如果您在 SSMS 中运行之前的脚本(按 Ctrl+M -> 实际执行计划)，那么您将获得最后一个查询的执行计划:

在这种情况下，CTE 对 crt 别名执行一次，对 prev 别名执行五(!)次，对 crt< 的每一行执行一次.

所以，这个问题的答案

Does WITH statement execute once per query or once per row?

是both:每个查询一次(crt)和每行一次(prev:每个来自crt)。

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

2) 将此表的主键定义为连接列，

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

当然，您可以尝试在没有 CTE 之间的这种自连接的情况下重写最终查询。