语句执行一次查询还是每一行执行一次?

My understanding of the WITH statement (CTE) is that it executes once per query. With a query like this:

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

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

If this results in 100 rows, I expect that Query1 was executed only once - not 100 times. If that assumption is correct, the time taken to run the entire query is roughly equal to the time taken to: run Query1 + select from SomeTable + join SomeTable to Query1.

如果结果是100行，我希望Query1只执行一次，而不是100次。如果这个假设是正确的，那么运行整个查询所需的时间大约等于:运行Query1 + select from SomeTable + join SomeTable到Query1所需的时间。

I am in a situation where:

我现在的情况是:

Query1 when run alone takes ~5 seconds (400k rows).
独自运行的Query1需要大约5秒(400k行)。
The remainder of the query, after removing the WITH statement and the LEFT JOIN takes ~15 seconds (400k rows).
查询的其余部分，在删除了WITH语句之后，剩下的连接需要15秒(400k行)。

So, when running the entire query with the WITH statement and the LEFT JOIN in place, I would have expected the query to complete in a timely manner, instead I've let it run for over an hour and once stopped it only got as far as 11k rows.

因此，当使用with语句和左连接运行整个查询时，我希望查询能够及时完成，而不是让它运行一个多小时，一旦停止查询，它只会运行11k行。

I am clearly wrong, but why?

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

1 个解决方案

#1

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: 语句执行一次查询还是每一行执行一次?

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

In this case, the CTE is executed one time for crt alias and five (!) times for prev alias, once for every row from crt.

在这种情况下，CTE被执行一次crt别名和5(!)次的prev别名，一次用于crt的每一行。

So, the answer for this question

这个问题的答案

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

语句执行一次查询还是每一行执行一次?

is both: once per query (crt) and once per row (prev: once for every for from crt).

都是:每个查询(crt)和每一行(prev:每个crt的一次)。

To optimize this query, for the start, 1) You can try to store the results from CTE (MyCTE or Query) into a table variable or a temp table

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

2) Define the primary key of this table as been the join colum(s),

2)将该表的主键定义为join colum(s)，

3) Rewrite the final query to use this table variable or temp table.

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

Off course, you can try to rewrite the final query without this self join between CTE.

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

#1