选择子查询的 COUNT(*) 次而不运行两次

Question

我有一个返回结果集的过程，该结果集受页码和其他一些内容的限制。 作为 OUTPUT 参数，我需要根据除页码之外的参数返回所选行的总数。 所以我有类似的东西：

WITH SelectedItems AS
(SELECT Id, Row1, Row2, ROW_NUMBER() OVER (ORDER BY Row1) AS Position
FROM Items
WHERE Row2 = @Row2)
SELECT Id, Row1, Row2
FROM SelectedItems
WHERE Position BETWEEN @From AND @To

然后我需要将 OUTPUT 参数设置为内部查询中的行数。 我可以复制查询并对其进行计数，但此查询可能会返回数千行（并且将来会更多），因此我正在寻找具有良好性能的方法。 我在考虑表变量，这是个好主意吗？ 或者还有其他建议吗？

更具体地说，它是 Microsoft SQL Server 2008。

谢谢，Jan

Answer 1

您可以使用 COUNT(*) 将总行数作为主查询中的单独列进行计数。 像这样：

WITH SelectedItems AS
(SELECT Id, Row1, Row2, ROW_NUMBER() OVER (ORDER BY Row1) AS Position, 
COUNT(*) OVER () AS TotalRows
FROM Items
WHERE Row2 = @Row2)
SELECT Id, Row1, Row2
FROM SelectedItems
WHERE Position BETWEEN @From AND @To

这将返回结果集中的计数，而不是输出参数中的计数，但这应该符合您的要求。 否则，与临时表结合使用：

DECLARE @tmp TABLE (Id int, RowNum int, TotalRows int);

WITH SelectedItems AS
(SELECT Id, Row1, Row2, ROW_NUMBER() OVER (ORDER BY Row1) AS Position, 
COUNT(*) OVER () AS TotalRows
FROM Items
WHERE Row2 = @Row2)
INSERT @tmp
SELECT Id, Row1, Row2
FROM SelectedItems
WHERE Position BETWEEN @From AND @To

SELECT TOP 1 @TotalRows = TotalRows FROM @tmp
SELECT * FROM @tmp

您会发现仅对分页结果使用临时表不会使用太多内存（当然取决于您的页面大小），并且您只能将其保留很短的一段时间。 从临时表中选择完整结果集并选择 TotalRows 只会花费一点点时间。

这比运行完全独立的查询要快得多，在我的测试中（重复WITH）使执行时间加倍。

Answer 2

我认为你应该在单独的查询中执行此操作。 虽然这两个查询可能看起来几乎相同，但查询优化器处理它们的方式会有很大不同。

理论上，SQL Server 甚至可能无法遍历子查询中的所有行来对其进行计数。

Answer 3

我现在无法访问我的代码库，但我相信您可以使用 COUNT() OVER （或类似的命令）来返回总行数作为子查询的一部分。 然后您可以将其作为最终结果集的一部分返回。 它在每一行中都会重复，但在我看来，对于使用分页的应用程序来说，这只是一个较小的性能影响，而且最终结果应该有限。

几个小时后我将发布确切的代码。

编辑：这是我用来生成计数的行。 最后，我们的开发人员想要一个单独的方法来自行获取计数，因此现在我在同一存储过程中的两个位置维护搜索条件。

COUNT(*) OVER (PARTITION BY '') AS TotalCount

将其添加到您的 CTE，然后您可以选择 TotalCount，它将成为每行中的一列。

Answer 4

您必须在不限制范围的情况下运行整个查询至少一次才能获得完整的行数。 由于无论如何您都将执行此操作，因此您应该选择 @@RowCount 以便输出找到的总行数，而不是在每行中使用冗余的 count(*) 列使数据读取器超载。

1. 第一次运行 NEW 查询时：

select YOUR_COLUMNS 
from YOUR_TABLE 
where YOUR_SEARCH_CONDITION 
order by YOUR_COLUMN_ORDERING_LIST;
select @@rowcount;

2. 仅读取前 X 行

上述查询避免了 SqlDataReader 中出现冗余 COUNT(*) 列，否则每次调用 SqlDataReader.Read() 时都会发送该列。 由于您是第一次运行查询...而不是选择范围，只需读取前 X 行。这正是您想要的...完整结果计数、前 X 条记录，以及结果集的高效流式传输，无需冗余计数列。

3. 对于后续运行 SAME 查询以获取结果的子集

select YOUR_COLUMNS 
from (select YOUR_COLUMNS, ROW_NUMBER() 
over(order by BY YOUR_COLUMN_ORDERING_LIST) as RowNum) Results 
where Results.RowNum between @From and @To;

在任何情况下，@@rowcount 是访问第一次运行查询时的计数而不限制结果的最直接方法设置（无论如何你都会想要前 X 个结果），而不运行单独的 count() 查询，不使用临时表，也不包含冗余的 count() 列。

Answer 5

您不能将输出变量设置为@@RowCount 吗？ 这将获取受最后执行的语句影响的行：

SELECT stuff FROM mytable

SET @output = @@ROWCOUNT

这应该为您提供所需的内容，并且不涉及再次运行查询。