Эффективный способ получения @@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 из этого и использую запрос для этого, чтобы вернуть страницу необходимых мне данных.