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