ROW_NUMBER() OVER (ORDER BY ) is very slow with large dataset

Question

ROW_NUMBER() OVER (ORDER BY ) is very slow with large dataset

jdp12383

Old Hand

Points: 337
More actions
August 2, 2010 at 11:42 pm

#227371

Hi,
I am using ROW_NUMBER() OVER (ORDER BY columnName ASC) AS RowNumber to retrieve the specific page from database. This columnName can be different specified by the client application. Have a look at below sample. The below given is just an example and I have 17 (34 if we count Ascending and Descending both conditions) columns in the ROW_NUMBER section.
SELECT *
FROM
(
SELECT *, ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @pSortFieldName = 'column1'
AND @pSortDirection = 1 THEN column1 END ASC,
CASE WHEN @pSortFieldName = 'column2'
AND @pSortDirection = 1 THEN column2 END ASC,
CASE WHEN @pSortFieldName = 'column1'
AND @pSortDirection = 0 THEN column1 END DESC,
CASE WHEN @pSortFieldName = 'column2'
AND @pSortDirection = 0 THEN column2 END DESC) RowNumber
FROM table1 INNER JOIN
table2 ON table1.column3 = table2.column3
) t2
WHERE RowNumber BETWEEN 1 TO 50
Now the ROW_NUMBER() is very slow when used with large dataset. I have 1000K records in my table and trying to retrieve 50 records page only. If I try without ROW_NUMBER() then it takes only 3 seconds otherwise it is taking very long in minutes.
Is there any better approach to achieve this? Any suggestions, article, reference doc would be greatly appreciated.
Thanks,
Jigs

Viewing 5 posts - 1 through 4 (of 4 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Have a look through Paul White's excellent server-side paging articles. They describe your problem and how to get around it.

http://www.sqlservercentral.com/articles/paging/69892/[/url]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jdp12383 Old Hand Points: 337 More actions · Answer 2

Thanks for your reply. I really appreciate.

I read the article you suggested. It is really very informative. I will consider it as well while working on my problem.

Thanks,

Jigs

gofrancesc Hall of Fame Points: 3269 More actions · Answer 3

Why about changing your BETWEEN clause by a SELECT TOP?, try it:

SELECT TOP 50 *, ROW_NUMBER() OVER (

ORDER BY

CASE WHEN @pSortFieldName = 'column1'

AND @pSortDirection = 1 THEN column1 END ASC,

CASE WHEN @pSortFieldName = 'column2'

AND @pSortDirection = 1 THEN column2 END ASC,

CASE WHEN @pSortFieldName = 'column1'

AND @pSortDirection = 0 THEN column1 END DESC,

CASE WHEN @pSortFieldName = 'column2'

AND @pSortDirection = 0 THEN column2 END DESC) RowNumber

FROM table1 INNER JOIN

table2 ON table1.column3 = table2.column3

Regards,

Francesc

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Dynamic SQL is by far the best solution to this type of dynamic paging requirement, especially when combined with getting just the relevant keys in many-table scenarios and then getting the actually values with a second hit. I have used this at numerous clients to great (as in 3 to 4 orders of magnitude performance improvement) effect.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service