x

Consolidated: SQL 2005+ Dynamic Pagination using SET ROWCOUNT or RowNumber() ?

I've a complex SP which applies multiple JOINs and lookup and come complex filters like comma-separated values, etc... On top of it, I've to deploy two complex yet performance-effective features:

1. Dynamic sorting but I see its limited - you kno the long/clumsy CASE hierarchy, its strange that experts also agree that this is the only 'best' solution we've got:

http://stackoverflow.com/questions/149380/dynamic-sorting-within-sql-stored-procedures
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942

Anyway, I don't expect much on this one for now.

2. Dynamic pagination - that is I want the SP to be able to return only X number of records (X = page size) starting from Y (Y = page number). I hope you've got the general idea.

To make it more clear I want to use something available in MySQL & PostgreSQL:

[LIMIT { number | ALL }] [OFFSET number]

http://stackoverflow.com/questions/1545203/database-sql-pagination
http://www.sql.org/sql-database/postgresql/manual/queries-limit.html

Its strange such a simple & basic functionality is NOT available in SQL 2005+ .. or am I wrong (I'd be glad to hear it :-))


I've known two approaches which suite my performance\complexity tradeoff -

[2.1] Using the 'RowNumber()' feature of SQL 2005 and then applying filter: (I've used it in past)

WHERE (Row BETWEEN (@PageIndex-1) * @PageSize +1 AND @PageIndex* @PageSize)                    

But again, this needs creating a temp table or using a WITH clause. This is also explained in: http://stackoverflow.com/questions/187998/row-offset-in-ms-sql-server

[2.2] I found some new ways. One of them is using the -

SET ROWCOUNT

http://www.4guysfromrolla.com/webtech/042606-1.shtml

And they say that overall 2.2 is effective then 2.1. Is it? Also, I wanted to know what happens if two user-requests trigger the same SP twice simultaneously .. I hope the 'SET ROWCOUNT' won't be 'shared' or 'over-written' among simultaneous SP-calls. Pls confirm. Any other points comparing 2.1 & 2.2 ?

Pheew .. I hope I've done my homework in this consolidated analysis I've shared. Pls rate it and share your thots. Let me know if this has been useful.

Thank you.

more ▼

asked Nov 03, 2009 at 12:39 PM in Default

Hmnt gravatar image

Hmnt
43 4 5 5

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Paging is one of those things where there may not be a single "best method" for all circumstances. Efficient paging is a big topic and, similar to query optimization, can be somewhat of an art to perfect.

A dynamic sql solution may work well for a very flexible search SP with lots of optional parameters where the user's criteria will affect which tables are included in the FROM clause as well as dictate if a GROUP BY clause is necessary.

If the data is stored in a "pre-compiled" reporting server it may make sense to pre-rank the data by all possible sorting options. Obviously that is not possible in an OLTP system.

Sometimes a total number of rows is required (so you can show "page 2 of 102"). That requirement can throw a wrench into some cool solutions - like the nested TOP techniques.

The bottom line is that you need to compare ALL your solutions for execution times, number of reads and exectution plans for a reasonable range of possible calls. It is also important to look at those factors as well as server metrix under a concurrency load. Because what may work great for one call may crash and burn when 50 people are running it concurrently.

more ▼

answered Nov 03, 2009 at 03:58 PM

TG gravatar image

TG
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x58
x11
x6

asked: Nov 03, 2009 at 12:39 PM

Seen: 2027 times

Last Updated: Nov 05, 2009 at 01:00 AM