January 2, 2013 at 4:30 am
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;-)
January 2, 2013 at 4:35 am
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
January 2, 2013 at 4:37 am
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.
January 2, 2013 at 7:43 am
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
January 2, 2013 at 8:07 am
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
January 2, 2013 at 8:40 am
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
January 4, 2013 at 2:39 am
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;-)
January 4, 2013 at 3:58 am
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
January 4, 2013 at 6:34 am
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