SQL Server Performance Forum – Threads Archive

ROW_NUMBER() – Poor Performanced w 500,000 records

I’m currently using a paging stored procedure that has great performance w/ 500,000 + records. Since I’ve upgraded to SQL 2005, I wanted to give the ROW_Number() method of paging since it was supposed to be the primary method for paging in 2005. Unfortunately, it resulted in my database server getting slammed with a huge decrease in performance. The method I implemented was the following:http://aspnet.4guysfromrolla.com/articles/031506-1.aspx I even added TOP paging to prevent more rows from being counted. The current method that i’m using has been working for years with the best performance over any other method i’ve found to date:http://rosca.net/writing/articles/SelectWithPaging.sql.txt So far i’ve found one reference to ROW_NUMBER() being poor performance with large result sets at this site:http://weblogs.asp.net/eporter/arch…ER-Not-Fast-Enough-With-Large-Result-Set.aspx Can anyone confirm the current problems with ROW_NUMBER() ?

If you have a lot of records, using TOP X in the inner SELECT clause may speed up things a bit as there is no use returning 1000 records. Depending upon the records request I would go with paging solution in this case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

TOP X has no effect in this case :/

Have you looked at the execution plan? Do you have an index on the Order By column?
Roji. P. Thomas
http://toponewithties.blogspot.com

On a sidenote: I find it quite amazing to believe that anyone is willing to browse through a 500k resultset. I certainly won’t, but rather would narrow down my search drastically. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

When we were rewriting our paging approach a couple of years ago, one of the great observation that helped us is that 90% of the time the user will not go beuond the first page. Roji. P. Thomas
http://toponewithties.blogspot.com

Would it help to add the FAST n hint in such a case? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Do you need pagination? http://www.aspfaq.com/show.asp?id=2120 Madhivanan Failing to plan is Planning to fail

I have a good pagnation stored procedure. I’m just wondering why using ROW_Number() performs much much worse in this case. Yes, the order by is a clustered index. In my execution plan it shows all the usage in Segment and Clustered Index Scan… Segment
Estimated CPU Cost: 0.0092299
Estimated I/O Cost: 0
Estimated Number of Rows: 100
Estimated Operator Cost: 0.0092399 (138%)
Estimated Rebinds: 0
Estimated Rewinds: 0
Estimated Row Size: 273B
Estimated Subtree Cost: 0.0158284 Clustered Indes Scan
Estimated CPU Cost: 0.5078
Estimated I/O Cost: 14.9039
Estimated Number of Rows: 100
Estimated Operator Cost: 0.0065885 (98%)
Estimated Rebinds: 0
Estimated Rewinds: 0
Estimated Row Size: 265B
Estimated Subtree Cost: 0.0065885

]]>