CODEWITHSUNDEEP
performancepaginationsql-server-2012
CrystalBlue
CrystalBlue

Reputation: 1863

Getting total row count from OFFSET / FETCH NEXT

So, I've got a function that returns a number of records that I want to implement paging for on my website. It was suggested to me that I use the Offset/Fetch Next in SQL Server 2012 to accomplish this. On our website, we have an area that lists total number of records and what page you're on at the time.

Before, I was getting the entire record set and was able to build the paging on that programatically. But using the SQL way with FETCH NEXT X ROWS ONLY, I am only given back X rows, so I don't know what my total record set is and how to calculate my min and max pages. The only way I can tell of doing this is calling the function twice and doing a count of rows on the first, then running the second with FETCH NEXT. Is there a better way that won't have me running the query twice? I am trying to speed up performance, not slow it down.

Upvotes: 119

Views: 88568

Answers (4)

Louis Somers
Louis Somers

Reputation: 2964

Apparently results can vary vastly depending on the query. I tested my case with these results: (8 joins, 2 sub queries, 5800 rows in distinct result, 5900 non-distinct):

  • ~0.820 sec using COUNT(1) OVER() (Aaron Bertrand's answer, but with wrong results*)
  • ~0.850 sec using #TEMP table.
  • ~1.590 sec WITH .. AS (James Moberg's anser)
  • ~1.600 sec running twice (first time without ordering, just to count)

*In my case Aaron Bertrand's answer did not work out because COUNT(1) OVER() seems to include the rows filtered out by DISTINCT.

Using a temp table:

DECLARE 
  @PageSize INT = 10, 
  @PageNum  INT = 1;
 
SELECT
  name, object_id
INTO #MY_TEMP
FROM sys.all_objects

SELECT *
FROM #MY_TEMP
ORDER BY name
  OFFSET (@PageNum-1)*@PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;

SELECT COUNT(1) FROM #MY_TEMP
-- or
-- SELECT @MY_OUTPUT_PARAM = COUNT(1) FROM #MY_TEMP

DROP TABLE #MY_TEMP

Nice thing about the temp table is that the count can be separated into a different result or output parameter.

Upvotes: 4

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You can use COUNT(*) OVER() ... here is a quick example using 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;

However, this should be reserved for small data sets; on larger sets, the performance can be abysmal. See this Paul White article for better alternatives, including maintaining indexed views (which only works if the result is unfiltered or you know WHERE clauses in advance) and using ROW_NUMBER() tricks.

Upvotes: 143

elblogdelbeto
elblogdelbeto

Reputation: 166

Based on James Moberg's answer:

This is an alternative using Row_Number(), if you don't have SQL server 2012 and you can't use 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

Upvotes: 1

James Moberg
James Moberg

Reputation: 4475

I encountered some performance issues using the COUNT() OVER() method. (I'm not sure if it was the server as it took 40 seconds to return 10 records and then later didn't have any issues.) This technique worked under all conditions without having to use COUNT() OVER() and accomplishes the same thing:

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

Upvotes: 171

Related Questions