New PAGING Funtion comparison with TOP

  • i have compared the new paging function with top query but found NO positve response here. The paging query gave negative impact.

    Queries i used :

    SELECT TOP 100

    [TransactionID]

    ,[ProductID]

    ,[ReferenceOrderID]

    ,[ReferenceOrderLineID]

    ,[TransactionDate]

    ,[TransactionType]

    ,[Quantity]

    ,[ActualCost]

    ,[ModifiedDate]

    FROM [Production].[TransactionHistoryArchive]

    WHERE [TransactionID] >= 5001

    ORDER BY [TransactionID]

    SELECT

    [TransactionID]

    ,[ProductID]

    ,[ReferenceOrderID]

    ,[ReferenceOrderLineID]

    ,[TransactionDate]

    ,[TransactionType]

    ,[Quantity]

    ,[ActualCost]

    ,[ModifiedDate]

    FROM [Production].[TransactionHistoryArchive]

    ORDER BY [TransactionID]

    OFFSET 5001 ROWS

    FETCH NEXT 100 ROWS ONLY

    TOP query's Actual number of rows : 100

    PAGING query's Actual number of rows : 5101

    (100 row(s) affected)

    Table 'TransactionHistoryArchive'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (100 row(s) affected)

    Table 'TransactionHistoryArchive'. Scan count 1, logical reads 41, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Hi ,

    To my mind these are two fundementally different queries.

    Just because you have a sequential transactionID which means you happen to get the same result, this is an unfair comparison.

    FYI : My latest blog on the subject is http://sqlblogcasts.com/blogs/sqlandthelike/archive/2012/04/26/offset-without-offset.aspx

    Dave



    Clear Sky SQL
    My Blog[/url]

  • Dave Ballantyne (1/2/2013)


    Hi ,

    To my mind these are two fundementally different queries.

    Just because you have a sequential transactionID which means you happen to get the same result, this is an unfair comparison.

    FYI : My latest blog on the subject is http://sqlblogcasts.com/blogs/sqlandthelike/archive/2012/04/26/offset-without-offset.aspx

    Dave

    +1

    The first is filtering so SQL knows where to grab the top 100 rows

    The second is having to read the whole table then find row 5001 and then give you the next 100.

    Two completly different queries, which will give two completly different ways of doing the same thing.

  • A slight tweak to your second query will show you how similar OFFSET and TOP can be:

    USE AdventureWorks2012

    SELECT TOP 100

    [TransactionID]

    ,[ProductID]

    ,[ReferenceOrderID]

    ,[ReferenceOrderLineID]

    ,[TransactionDate]

    ,[TransactionType]

    ,[Quantity]

    ,[ActualCost]

    ,[ModifiedDate]

    FROM [Production].[TransactionHistoryArchive]

    WHERE [TransactionID] >= 5001

    ORDER BY [TransactionID]

    SELECT

    [TransactionID]

    ,[ProductID]

    ,[ReferenceOrderID]

    ,[ReferenceOrderLineID]

    ,[TransactionDate]

    ,[TransactionType]

    ,[Quantity]

    ,[ActualCost]

    ,[ModifiedDate]

    FROM [Production].[TransactionHistoryArchive]

    WHERE [TransactionID] >= 5001

    ORDER BY [TransactionID]

    OFFSET 1 ROWS

    FETCH NEXT 100 ROWS ONLY

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (1/2/2013)


    A slight tweak to your second query will show you how similar OFFSET and TOP can be:

    USE AdventureWorks2012

    SELECT TOP 100

    [TransactionID]

    ,[ProductID]

    ,[ReferenceOrderID]

    ,[ReferenceOrderLineID]

    ,[TransactionDate]

    ,[TransactionType]

    ,[Quantity]

    ,[ActualCost]

    ,[ModifiedDate]

    FROM [Production].[TransactionHistoryArchive]

    WHERE [TransactionID] >= 5001

    ORDER BY [TransactionID]

    SELECT

    [TransactionID]

    ,[ProductID]

    ,[ReferenceOrderID]

    ,[ReferenceOrderLineID]

    ,[TransactionDate]

    ,[TransactionType]

    ,[Quantity]

    ,[ActualCost]

    ,[ModifiedDate]

    FROM [Production].[TransactionHistoryArchive]

    WHERE [TransactionID] >= 5001

    ORDER BY [TransactionID]

    OFFSET 1 ROWS

    FETCH NEXT 100 ROWS ONLY

    In order to have exactly the same query, you should change the second from:

    OFFSET 1 ROWS

    FETCH NEXT 100 ROWS ONLY

    to:

    OFFSET 0 ROWS

    FETCH NEXT 100 ROWS ONLY

  • e4d4 (1/2/2013)


    opc.three (1/2/2013)


    A slight tweak to your second query will show you how similar OFFSET and TOP can be:

    USE AdventureWorks2012

    SELECT TOP 100

    [TransactionID]

    ,[ProductID]

    ,[ReferenceOrderID]

    ,[ReferenceOrderLineID]

    ,[TransactionDate]

    ,[TransactionType]

    ,[Quantity]

    ,[ActualCost]

    ,[ModifiedDate]

    FROM [Production].[TransactionHistoryArchive]

    WHERE [TransactionID] >= 5001

    ORDER BY [TransactionID]

    SELECT

    [TransactionID]

    ,[ProductID]

    ,[ReferenceOrderID]

    ,[ReferenceOrderLineID]

    ,[TransactionDate]

    ,[TransactionType]

    ,[Quantity]

    ,[ActualCost]

    ,[ModifiedDate]

    FROM [Production].[TransactionHistoryArchive]

    WHERE [TransactionID] >= 5001

    ORDER BY [TransactionID]

    OFFSET 1 ROWS

    FETCH NEXT 100 ROWS ONLY

    In order to have exactly the same query, you should change the second from:

    OFFSET 1 ROWS

    FETCH NEXT 100 ROWS ONLY

    to:

    OFFSET 0 ROWS

    FETCH NEXT 100 ROWS ONLY

    Good catch on the one row offset. I was only looking at the execution plans to show that OFFSET can replace TOP in many instances, TOP being proprietary, OFFSET being ANSI-Standard SQL.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (1/2/2013)


    I was only looking at the execution plans to show that OFFSET can replace TOP in many instances, TOP being proprietary, OFFSET being ANSI-Standard SQL.

    opc, can you please explain this ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • looking at both queries i agree they are different.

    If you imagine the first 100 records had been deleted and there were no other gaps in the TransactionID number sequence

    The first query would return a 100 rows greater than 5001

    but the second query would return the 100 rows after 5101 because of the 100 deleted records.

    hope this helps explain why the queries are different. The performance of paging may be slower but you dont have the management of deleted rows to consider. IMO

  • Bhuvnesh (1/4/2013)


    opc.three (1/2/2013)


    I was only looking at the execution plans to show that OFFSET can replace TOP in many instances, TOP being proprietary, OFFSET being ANSI-Standard SQL.

    opc, can you please explain this ?

    TOP is an extension of SQL added in the days when SQL Server was still a Sybase-only product, i.e. it is a proprietary piece of Transact-SQL. OFFSET by comparison offers some functional overlap to TOP and is an ANSI-standard piece of Transact-SQL.

    If you have a look at the execution plans of the two queries I posted you'll see that they are rationalized the same way by the optimizer. Specifically you'll see the Top Operator used in both plans.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply