Эффективный способ получения @@rowcount из запроса с помощью row_number

У меня есть дорогостоящий запрос с использованием функции row_number over() в SQL Server 2005. Я возвращаю только вспомогательный список этих записей, поскольку запрос разбивается на страницы. Однако я хотел бы также вернуть общее количество записей, а не только разбитое на разделы подмножество. Запуск запроса эффективно дважды, чтобы получить счет, не может быть и речи.

Выбор счетчика (*) также не может быть и речи, поскольку производительность абсолютно ужасная, когда я пробовал это.

То, что я действительно люблю, - @@ROW_NUMBERROWCOUNT: -)

Ответ 1

Проверьте агрегат COUNT (*) при использовании с OVER (PARTITON BY..), например:

    SELECT
     ROW_NUMBER() OVER(ORDER BY object_id, column_id) as RowNum
    , COUNT(*) OVER(PARTITION BY 1) as TotalRows
    , * 
    FROM master.sys.columns

Это ИМХО лучший способ сделать это без необходимости делать два запроса.

Ответ 2

На протяжении многих лет куча пота разработчика пополнялась эффективными наборами результатов поискового вызова. Тем не менее, ответа нет - это зависит от вашего варианта использования. Часть прецедента эффективно обрабатывает вашу страницу, часть выясняет, сколько строк находится в полном наборе результатов. Так что извините, если я немного отклонился от подкачки, но эти два довольно тесно связаны в моем сознании.

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

Запустить отдельный `Count(*)`

запустите отдельный запрос, который выполняет простой "select count (*) из MyTable"
просто и удобно для небольшой таблицы
хорошо на нефильтрованной большой таблице, которая либо узкая, либо имеет компактный некластеризованный индекс, который вы можете использовать
ломается, когда у вас сложный критерий WHERE/JOIN, потому что выполнение WHERE/JOIN вдвое дороже.
ломается по широкому индексу, так как число чтений увеличивается.

Объединить `ROW_Number() OVER()` и `COUNT(1) OVER(PARTITION By 1)`

Это было предложено @RBarryYoung. Он имеет преимущество быть простым реализовать и очень гибко.
С другой стороны, существует множество причин, по которым это может стать чрезвычайно дорогостоящим.
Например, в базе данных, в которой я сейчас работаю, есть таблица Media с примерно 6000 строк. Он не особенно широк, имеет целочисленный кластерный ПК и, а также компактный уникальный индекс. Тем не менее, простая COUNT(*) OVER(PARTITION BY 1) as TotalRows приводит к ~ 12 000 чтений. Сравните это с простым SELECT COUNT(*) FROM Media - 12 прочтением. Wowzers.

UPDATE - проблема с чтением, о которой я упоминал, немного красно-селедка. Оказывается, что с оконными функциями блок, используемый для измерения показаний, является смешанным. Конечным результатом является то, что, как представляется, представляет собой огромное количество чтений. Вы можете узнать больше о проблеме здесь: Почему логические чтения для оконных агрегатных функций настолько высоки?

Таблицы Temp/Табличные переменные

Существует множество стратегий, которые принимают набор результатов и вставляют соответствующие ключи или сегменты результатов в переменные temp tables/table.
Для небольших/средних размерных наборов это может обеспечить отличные результаты.
Этот тип стратегии работает практически с любой платформой/версией SQL.
Работа с результирующим множеством несколько раз (нередко требование) также легка.
Нижняя сторона - это работа с большими наборами результатов... вставка нескольких миллионов строк в временную таблицу имеет стоимость.
Усугубляя проблему, при высоком уровне громкости системное давление на TempDB может быть довольно важным фактором, а temp-таблицы эффективно работают в TempDB.

Гауссовская сумма/двойной номер строки

Эта идея основана на подмножестве чего-то, что вычислил математик Гаусс (как суммировать серию чисел). Подмножество состоит в том, как получить количество строк из любой точки таблицы.
Из серии чисел (Row_Number()) число строк в строке от 1 до N равно (N + 1) - 1. Больше объяснений в ссылках.
Формула кажется, что она будет чистым только до N, но если вы придерживаетесь формулы, то происходит интересное, вы можете подсчитать количество строк со страницы в середине таблицы.
Конечный результат: вы делаете ROW_Number() OVER(Order by ID) и ROW_Number() OVER(Order by ID DESC), затем суммируете два числа и вычитаете 1.
Используя таблицу мультимедиа в качестве примера, мои чтения упали с 12 000 до 75.
На более крупной странице вы много раз повторяли данные, но смещение в чтениях может стоить того.
Я не тестировал это на слишком многих сценариях, поэтому он может разваливаться в других сценариях.

Верх (@n)/SET ROWCOUNT

Это не конкретные стратегии, а оптимизация, основанная на том, что мы знаем о оптимизаторе запросов.
Творчески используя Top (@n) [top может быть переменной в SQL 2008] или SET ROWCOUNT может уменьшить ваш рабочий набор... даже если вы вытаскиваете среднюю страницу результирующего набора, вы все равно можете сузить результат
Эти идеи работают из-за поведения оптимизатора запросов... пакет обновления/исправление может изменить поведение (хотя, вероятно, нет).
В экземплярах certian SET ROWCOUNT может быть немного точным.
Эта стратегия не учитывает получение полного количества строк, просто делает пейджинг более эффективным

Итак, что делать разработчику?

Прочитайте моего хорошего человека, читайте. Вот некоторые статьи, на которые я склонялся...

Надеюсь, что это поможет.

Ответ 3

Если счетчик (*) медленный, вам действительно нужно сначала решить эту проблему, тщательно изучив ваши индексы и убедившись, что ваши статистические данные актуальны.

По моему опыту, нет ничего лучше, чем делать два отдельных запроса: один для получения страницы данных и один, чтобы получить общее количество. Использование временной таблицы для получения итоговых значений является стратегией проигрыша по мере увеличения количества строк. Например, стоимость вставки 10 000 000 000 строк в временную таблицу просто для их подсчета явно будет чрезмерной.

Ответ 4

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