Получение итогового количества строк из OFFSET/FETCH NEXT

Итак, у меня есть функция, которая возвращает несколько записей, которые я хочу реализовать подкачки на моем сайте. Мне было предложено, чтобы я использовал Offset/Fetch Next в SQL Server 2012 для выполнения этого. На нашем веб-сайте у нас есть область, в которой указано общее количество записей и какая страница вы в это время.

Раньше я получал весь набор записей и смог запрограммировать пейджинг. Но используя метод SQL с FETCH NEXT X ROWS ONLY, мне дают только X строк, поэтому я не знаю, что такое мой общий набор записей и как рассчитать мои минимальные и максимальные страницы. Единственный способ, которым я могу это сказать, - вызвать функцию дважды и сделать счет строк на первом, а затем запустить второй с FETCH NEXT. Есть ли лучший способ, который не заставит меня выполнить запрос дважды? Я пытаюсь ускорить работу, а не замедлять ее.

Ответы

Ответ 1

Вы можете использовать COUNT(*) OVER()... вот краткий пример использования sys.all_objects:

DECLARE 
  @PageSize INT = 10, 
  @PageNum  INT = 1;

SELECT 
  name, object_id, 
  overall_count = COUNT(*) OVER()
FROM sys.all_objects
ORDER BY name
  OFFSET (@PageNum-1)*@PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;

Однако это должно быть зарезервировано для небольших наборов данных; на больших комплектах производительность может быть ужасной. Смотрите эту статью Пола Уайта, чтобы найти лучшие альтернативы, включая поддержку индексированных представлений (что работает только в том случае, если результат не отфильтрован или вы заранее знаете предложения WHERE) и использование приемов ROW_NUMBER().

Ответ 2

Я столкнулся с некоторыми проблемами производительности, используя метод COUNT() OVER(). (Я не уверен, что это был сервер, так как потребовалось 40 секунд, чтобы вернуть 10 записей, а затем не было никаких проблем.) Этот метод работал при любых условиях без использования COUNT() OVER() и выполняет то же самое:

DECLARE 
    @PageSize INT = 10, 
    @PageNum  INT = 1;

WITH TempResult AS(
    SELECT ID, Name
    FROM Table
), TempCount AS (
    SELECT COUNT(*) AS MaxRows FROM TempResult
)
SELECT *
FROM TempResult, TempCount
ORDER BY TempResult.Name
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY

Ответ 3

На основе Ответ Джеймса Моберга:

Это альтернатива с использованием Row_Number(), если у вас нет SQL-сервера 2012, и вы не можете использовать OFFSET

DECLARE 
    @PageNumEnd INT = 10, 
    @PageNum  INT = 1;

WITH TempResult AS(
    SELECT ID, NAME
    FROM Tabla
), TempCount AS (
    SELECT COUNT(*) AS MaxRows FROM TempResult
)

select * 
from
(
    SELECT
     ROW_NUMBER() OVER ( ORDER BY PolizaId DESC) AS 'NumeroRenglon', 
     MaxRows, 
     ID,
     Name
    FROM TempResult, TempCount

)resultados
WHERE   NumeroRenglon >= @PageNum
    AND NumeroRenglon <= @PageNumEnd
ORDER BY NumeroRenglon