Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pagination options SQL 2012 vs 2008 (Performance perspective) Expand / Collapse
Author
Message
Posted Monday, September 2, 2013 4:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 10:41 PM
Points: 45, Visits: 289
I need to understand which one is better with respect to performance :

1. SQL 2012 has introducted FETCH OFFSET ...

SELECT FirstNm,LastNm
FROM Person
ORDER BY FirstNm
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY

2. SQL 2008 has an option of ROW_NUMBER() and then pick up specific row numbers. Sample below -
SELECT * FROM
(SELECT t1.colX, t2.colY
ROW_NUMBER() OVER (ORDER BY t1.col3) AS row
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.col1=t2.col2
)a
WHERE row >= n AND row <= m

Note: there is an index of t1.Col3.
Post #1490577
Posted Monday, September 2, 2013 5:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 23,003, Visits: 31,495
npranj (9/2/2013)
I need to understand which one is better with respect to performance :

1. SQL 2012 has introducted FETCH OFFSET ...

SELECT FirstNm,LastNm
FROM Person
ORDER BY FirstNm
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY

2. SQL 2008 has an option of ROW_NUMBER() and then pick up specific row numbers. Sample below -
SELECT * FROM
(SELECT t1.colX, t2.colY
ROW_NUMBER() OVER (ORDER BY t1.col3) AS row
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.col1=t2.col2
)a
WHERE row >= n AND row <= m

Note: there is an index of t1.Col3.


Best thing you could do is test them. What works best in one situation may not work the best in another.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1490594
Posted Tuesday, September 3, 2013 8:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:58 AM
Points: 1,270, Visits: 1,540
npranj (9/2/2013)
I need to understand which one is better with respect to performance :

1. SQL 2012 has introducted FETCH OFFSET ...

SELECT FirstNm,LastNm
FROM Person
ORDER BY FirstNm
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY

2. SQL 2008 has an option of ROW_NUMBER() and then pick up specific row numbers. Sample below -
SELECT * FROM
(SELECT t1.colX, t2.colY
ROW_NUMBER() OVER (ORDER BY t1.col3) AS row
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.col1=t2.col2
)a
WHERE row >= n AND row <= m

Note: there is an index of t1.Col3.


Having tried this on my setup, the new method is slightly better and IMO more readable.

Here is an external link that may provide more insight...
http://www.dbadiaries.com/new-t-sql-features-in-sql-server-2012-offset-and-fetch


_____________________________________________________________________________________
gsc_dba
Post #1490982
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse