New PAGING Funtion comparison with TOP

Question

Post reply

New PAGING Funtion comparison with TOP

Bhuvnesh

SSC Guru

Points: 59351
More actions
January 2, 2013 at 4:30 am

#268802

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;-)

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

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

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]

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

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.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

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

e4d4 SSCertifiable Points: 5777 More actions · Answer 4

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

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;-)

Terry300577 Hall of Fame Points: 3391 More actions · Answer 7

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

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