The Infamous Paging Algorithm (T-SQL)
Posted on 2005-05-11
OK, so I've seen a lot of questions and answers to this one, but i have yet to see the best solution (for my particular case). I am needing to page very large record sets (sometimes hundreds of thousands of rows). I need this T-SQL algorithm to be used in several contexts. One would be a full text search on our products from the website. The other is from our internal software which returns the largest results (upwards of 500,000 rows and growing), with fairly large pages (sometimes 1,000 rows/pg) as well.
- I've used the cursor method, but I'm not a fan of cursors unless they are absolutely necessary.
- I've tried the nested TOP SQL statements, but if you don't have a sequntial primary key in your results, then you can't pull this off. (am I right on this?) I have a PK obviously, but my results will never return sequential pk rows.
- I am currently using the temp table method, and I really like the idea of how this algorithm works, but the efficiency is still not what it needs to be. If I have 100 users on the website all searching for products, each search has to create a temp table, insert the rows, select my results and drop the table. And to add to this, our internal software will also be doing the same. The larger the results, the longer it takes to process this because each row is inserted into a table.
I know I can't be the only one to run into problems such as this one. Maybe I've exhausted my options and it's just a matter of CPU cost vs. Memory cost (while including several contributing factors)? Thoughts? Suggestions?