Pagination in SQL - Performance issue

Question

Am trying to use pagination and i got the perfect link in SO

https://stackoverflow.com/a/109290/1481690

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Exact same query am trying to use with additional join of few tables in my inner Query.

Am getting few performance issues in following scenarios

WHERE   RowNum >= 1
    AND RowNum < 20  ==>executes faster approx 2 sec


    WHERE   RowNum >= 1000
    AND RowNum < 1010      ==>  more time  approx 10 sec

    WHERE   RowNum >= 30000
    AND RowNum < 30010    ==> more time approx 17 sec

Everytime i select 10 rows but huge time difference. Any idea or suggestions ?

I chose this approach as am binding columns dynamically and forming Query. Is there any other better way i can organize the Pagination Query in SQl Server 2008.

Is there a way i can improve the performance of the query ?

Thanks

Answer 1

I always check how much data I am accessing in query and try to eliminate un necessary columns as well as rows. Well these are just obvious points you might have already check yet just wanted to pointed out in case you haven’t already. In your query the slow performance might be because you doing “Select *”. Selecting all columns from table does not allow to come with good Execution plan. Check if you need only selected columns and make sure you have correct covering index on table Orders.

Because explicit SKIPP or OFFSET function is not available in SQL 2008 version we need to create one and that we can create by INNER JOIN. In one query we will first generate ID with OrderDate and nothing else will be in that query. We do the same in second query but here we also select some other interested columns from table ORDER or ALL if you need ALL column. Then we JOIN this to query results by ID and OrderDate and ADD SKIPP rows filter for first query where data set is at its minimal size what is required. Try this code.

    SELECT q2.*
    FROM
    (
        SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, OrderDate
        FROM      Orders
        WHERE     OrderDate >= '1980-01-01'
    )q1
    INNER JOIN 
    (
        SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
        FROM      Orders
        WHERE     OrderDate >= '1980-01-01'
    )q2
        ON q1.RowNum=q2.RowNum AND q1.OrderDate=q2.OrderDate AND q1.rownum BETWEEN 30000 AND 30020

To give you the estimate, i tried this with following test data and no matter what window you query the results are back in less than 2 seconds, and note that the table is HEAP (no index) Table has total 2M rows. test select is querying 10 rows from 50,000 to 50,010

The below Insert took around 8 minutes.

    IF object_id('TestSelect','u') IS NOT NULL
        DROP TABLE TestSelect
    GO
    CREATE TABLE TestSelect
    (
        OrderDate   DATETIME2(2)
    )
    GO

    DECLARE @i bigint=1, @dt DATETIME2(2)='01/01/1700'
    WHILE @I<=2000000
    BEGIN

        IF @i%15 = 0
            SELECT @DT = DATEADD(DAY,1,@dt)

        INSERT INTO dbo.TestSelect( OrderDate )
        SELECT @dt

        SELECT @i=@i+1
    END

Selecting the window 50,000 to 50,010 took less than 3 seconds.

Selecting the last single row 2,000,000 to 2,000,000 also took 3 seconds.

    SELECT q2.*
    FROM
    (
        SELECT  ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum 
                ,OrderDate
        FROM TestSelect
        WHERE OrderDate >= '1700-01-01'
    )q1
    INNER JOIN
    (
        SELECT  ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum 
                ,*
        FROM TestSelect
        WHERE OrderDate >= '1700-01-01'
    )q2
        ON q1.RowNum=q2.RowNum 
        AND q1.OrderDate=q2.OrderDate 
        AND q1.RowNum BETWEEN 50000 AND 50010

Answer 2

ROW_NUMBER is crappy way of doing pagination as the cost of the operation grows extensively.

Instead you should use double ORDER BY clause.

Say you want to get records with ROW_NUMBER between 1200 and 1210. Instead of using ROW_NUMBER() OVER (...) and later binding the result in WHERE you should rather:

SELECT TOP(11) *
FROM (
    SELECT TOP(1210) *
    FROM [...]
    ORDER BY something ASC
) subQuery
ORDER BY something DESC.

Note that this query will give the result in reverse order. That shouldn't - generally speaking - be an issue as it's easy to reverse the set in the UI so i.e. C#, especially as the resulting set should be relatively small.

The latter is generally a lot faster. Note that the latter solution will be greatly improved by CLUSTERING (CREATE CLUSTERED INDEX ...) on the column you use to sort the query by.

Hope that helps.

Answer 3

Even though you always selecting the same number of rows, performance degrades when you want to select rows at the end of your data window. To get first 10 rows, the engine fetches just 10 rows; to get next 10 it has to fetch 20, discard first 10 , and return 10. To get 30000 -- 30010, it has to read all 30010, skip first 30k, and return 10.

Some tricks to improve performance (not a full list, building OLAP completely skipped). You mentioned joins; if that's possible join not inside the inner query, but result of it. You can also try to add some logic to ORDER BY OrderDate - ASC or DESC depends on what bucket you are retrieving . Say if you want to grab the "last" 10, ORDER BY ... DESC will work much faster. Needles to say, it has to be an index orderDate.

Answer 4

Incredibly, no other answer has mentioned the fastest way to do paging in all SQL Server versions, specifically with respect to the OP's question where offsets can be terribly slow for large page numbers as is benchmarked here.

There is an entirely different, much faster way to perform paging in SQL. This is often called the "seek method" as described in this blog post here.

SELECT TOP 10 *
FROM Orders
WHERE OrderDate >= '1980-01-01'
AND ((OrderDate > @previousOrderDate)
  OR (OrderDate = @previousOrderDate AND OrderId > @previousOrderId))
ORDER BY OrderDate ASC, OrderId ASC

The @previousOrderDate and @previousOrderId values are the respective values of the last record from the previous page. This allows you to fetch the "next" page. If the ORDER BY direction is DESC, simply use < instead.

With the above method, you cannot immediately jump to page 4 without having first fetched the previous 40 records. But often, you do not want to jump that far anyway. Instead, you get a much faster query that might be able to fetch data in constant time, depending on your indexing. Plus, your pages remain "stable", no matter if the underlying data changes (e.g. on page 1, while you're on page 4).

This is the best way to implement paging when lazy loading more data in web applications, for instance.

Note, the "seek method" is also called keyset paging.

Answer 5

declare @pageOffset int
declare @pageSize int
-- set variables at some point

declare @startRow int
set @startRow = @pageOffset * @pageSize

declare @endRow int
set @endRow + @pageSize - 1

SELECT 
    o.*
FROM
(
    SELECT 
        ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum
        , OrderId
    FROM      
        Orders
    WHERE     
        OrderDate >= '1980-01-01'
) q1
INNER JOIN Orders o
    on q1.OrderId = o.OrderId
where
    q1.RowNum between @startRow and @endRow
order by
    o.OrderDate

Answer 6

@peru, regarding if there is a better way and to build on the explanation provided by @a1ex07, try the following -

If the table has a unique identifier such as a numeric (order-id) or (order-date, order-index) upon which a compare (greater-than, less-than) operation can be performed then use that as an offset instead of the row-number.

For example if the table orders has 'order_id' as primary-key then -
To get the first ten results -
1.

select RowNum, order_id from   
( select 
ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, 
o.order_id 
from orders o where  o.order_id > 0 ;  
) 
tmp_qry where RowNum between 1 and 10 order by RowNum; // first 10 

Assuming that the last order-id returned was 17 then,

To select the next 10,
2.

select RowNum, order_id from   
( select 
ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, 
o.order_id 
from orders o where  o.order_id > 17 ;  
) 
tmp_qry where RowNum between 1 and 10 order by RowNum; // next 10 

Note that the row-num values have not been changed. Its the order-id value being compared that has been changed.

If such a key is not present then consider adding one !

Answer 7

Main drawback of your query is that it sorts whole table and calculates Row_Number for every query. You can make life easier for SQL Server by using less columns at sorting stage (for example as suggested by Anup Shah). However you still make it to read, sort and calculate row numbers for every query.

An alternative to calculations on the fly is reading values that were calculateed before.

Depending on volatility of your dataset and number of columns for sorting and filtering you can consider:

1. Add a rownumber column (or 2-3 columns ) and include it as a first columns in clustered index or create non-clustered inde).

2. Create views for most frequent combinations and then index those views. It is called indexed (materialised) views.

This will allow to read rownumber and performance will almost not depend on volume. Although maintaining of theese will, but less than sorting whole table for each query.

Note, that is this is a one off query and is run infrequently compared to all other queries, it is better to stick with query optimisation only: efforts to create extra columns/views might not pay off.