Выберите COUNT (*) подзапроса, не запуская его дважды

Вы можете подсчитать общее количество строк как отдельный столбец в своем основном запросе, используя 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) удвоил время выполнения.

Я думаю, вы должны сделать это в отдельном запросе. Хотя эти два запроса могут выглядеть почти одинаково, но способ их обработки оптимизатором запросов будет существенно различаться.

Теоретически SQL Server может даже не просмотреть все строки подзапроса, чтобы иметь возможность его подсчитать.

У меня сейчас нет доступа к моей кодовой базе, но я считаю, что вы можете использовать COUNT() OVER (или аналогичную команду), чтобы вернуть общее количество строк как часть подзапроса. Затем вы можете вернуть это как часть окончательного набора результатов. Он дублируется в каждой строке, но, на мой взгляд, это незначительное снижение производительности для приложения, использующего подкачку, и в любом случае должно иметь ограниченные конечные результаты.

Через пару часов выложу точный код.

РЕДАКТИРОВАТЬ: Вот строка, которую я использовал для подсчета. В конце концов, нашим разработчикам потребовался отдельный метод для получения подсчета, поэтому теперь я поддерживаю критерии поиска в двух местах в одной и той же хранимой процедуре.

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

Добавьте это в свой CTE, а затем вы можете выбрать TotalCount, и это будет столбец в каждой из ваших строк.

Вы ДОЛЖНЫ запустить весь запрос, не ограничивая диапазон, по крайней мере один раз, чтобы получить полное количество строк. Поскольку вы все равно собираетесь это сделать, вам следует выбрать @@RowCount, чтобы вывести общее количество найденных строк, а не перегружать средство чтения данных избыточным столбцом count(*) в каждой строке.

1. При первом запуске НОВОГО запроса:

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. Для последующих запусков ТАКОГО ЖЕ запроса, чтобы получить подмножество результатов

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().

Не могли бы вы просто установить выходную переменную в @@RowCount? Это позволит получить строки, затронутые последним выполненным оператором:

SELECT stuff FROM mytable

SET @output = @@ROWCOUNT

Это должно дать вам то, что вам нужно, и не требует повторного запуска запроса.