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

У меня есть процедура для возврата набора результатов, который ограничен номером страницы и некоторыми другими вещами. В качестве параметра 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 в число строк в innerquery. Я могу просто скопировать запрос и подсчитать его, но этот запрос может вернуть тысячи строк (и будет больше в будущем), поэтому я ищу способ сделать это с хорошей производительностью. Я думал о табличных переменных, это хорошая идея? Или любые другие предложения?

Чтобы быть более конкретным, это Microsoft SQL Server 2008.

Спасибо, Ян

Ответ 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

Вы найдете, что с помощью таблицы temp только ваш постраничный результат не будет использовать много памяти (в зависимости от размера вашей страницы, конечно), и вы будете держать его в живых в течение короткого периода времени. Выбор полного набора результатов из таблицы temp и выбор TotalRows займет чуть больше времени.

Это будет намного быстрее, чем запуск полностью отдельного запроса, который в моем тесте (повторение WITH) удвоил время выполнения.

Ответ 2

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

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

Ответ 3

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

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

EDIT: Здесь строка, которую я использовал для создания счета. В итоге наши разработчики хотели, чтобы отдельный метод получал счет сам по себе, поэтому теперь я поддерживаю критерии поиска в двух местах в одной и той же хранимой процедуре.

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

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

Ответ 4

Вы ДОЛЖНЫ запускать весь запрос, не ограничивая диапазон, по крайней мере один раз, чтобы получить полный счетчик строк. Поскольку вы все равно это сделаете, вы должны выбрать @@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. Для последующих прогонов запроса 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 является самым прямым способом доступа к счету при первом запуске запроса без ограничения набора результатов (ur хочет получить первые результаты X в любом случае), не запуская отдельный запрос count() без использования временной таблицы и без включения избыточного столбца count().

Ответ 5

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

SELECT stuff FROM mytable

SET @output = @@ROWCOUNT

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