|
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 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 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) 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.
(comments are locked)
|
|
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.
(comments are locked)
|
