sql - 在 CTE 内部使用 COUNT() 比在 CTE 外部使用更昂贵？

Question

我正在使用 SQL Server 进行分页，我想通过计算结果总数作为我的部分结果集的一部分来避免重复，而不是获取该结果集然后执行单独的查询以获取计数。然而，麻烦的是，它似乎增加了执行时间。例如，如果我使用 SET STATISTICS TIME ON 检查，则:

WITH PagedResults AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY AggregateId ASC) AS RowNumber,
        COUNT(PK_MatrixItemId) OVER() AS TotalRowCount,
        *
    FROM [MyTable] myTbl WITH(NOLOCK)
)
SELECT * FROM PagedResults
WHERE RowNumber BETWEEN 3 AND 4810

...或这个(其执行计划相同):

SELECT * FROM (
    SELECT TOP (4813)
        ROW_NUMBER() OVER (ORDER BY AggregateId ASC) AS RowNumber,
        COUNT(PK_MatrixItemId) OVER() AS TotalRowCount,
        *
    FROM [MyTable] myTbl WITH(NOLOCK)
) PagedResults
WHERE PagedResults.RowNumber BETWEEN 3 AND 4810

... 平均 CPU 时间(所有查询加起来)似乎是这个的 1.5 到 2 倍:

SELECT * FROM (
    SELECT TOP (4813)
        ROW_NUMBER() OVER (ORDER BY AggregateId ASC) AS RowNumber,
        *
    FROM [MyTable] myTbl WITH(NOLOCK)
) PagedResults
WHERE PagedResults.RowNumber BETWEEN 3 AND 4810

SELECT COUNT(*) FROM [MyTable] myTbl WITH(NOLOCK)

显然我宁愿使用前者而不是后者，因为后者冗余地重复了 FROM 子句(如果有的话，我会重复任何 WHERE 子句)，但是它的执行时间要好得多，我真的必须使用它。有什么办法可以完全缩短前者的执行时间吗？

Answer 1

CTE 内联到查询计划中。它们的性能与派生表完全相同。

派生表不对应物理操作。他们不会将结果集“具体化”到临时表中。 (我相信 MySQL 可以做到这一点，但 MySQL 是目前最原始的主流 ​​RDBMS。)

使用 OVER() 确实在查询计划中表现为对临时表的缓冲。完全不清楚为什么这会比重新读取基础表更快。缓冲相当慢，因为在 SQL Server 中写入比读取占用更多 CPU。我们可以从原始表中读取两次。这可能就是后一种选择更快的原因。

如果您想避免重复查询的某些部分，请使用 View 或表值函数。诚然，这些都不是一次性查询的好选择。也可以在应用层生成SQL，重复使用字符串。 ORM 也使这变得容易得多。