[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

The Infamous Paging Algorithm (T-SQL)

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?  

Thanks!
0
qumpus
Asked:
qumpus
  • 2
  • 2
1 Solution
 
Anthony PerkinsCommented:
The best solution based on performance and fexibility is using temporary tables (#TableName) or if you are using SQL Server 2000 variables of type table.  This allows you to sort in any direction on any column.

Just remember that you should be returning at most a few hundred rows.
0
 
qumpusAuthor Commented:
Thanks acperkins.

Is there a difference between creating the #temp table or using table datatype variables (I AM using SQL Server 2000)?
Is the temp table method even better than the cursor method?
Am I right about the nested TOP queries needing a sequential PK in the results?

I'm returning as few rows as the situation allows for.
0
 
dbeneitCommented:
I never use others two methods, but i run this
 >> 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.

That is easy to construct and it isn't slowly
if pk is a unique index
X is the number of page rows
Y is the page that you want show


select * from
(select top X field1,field2,... from
(select top Y*X field1,field2..... from
table where condition
order by field1 asc, field2 asc, PK_fieldk1 asc, PK_fieldk2 asc, ..,PK_fieldkn asc)  as firstXYrecords        <-- (at the end all unique index)
order by field1 desc, field2 desc, PK_fieldk1 desc, PK_fieldk2 desc, ..,PK_fieldkn desc) as OnlyXpagerecords
order by field1 asc, field2 asc, PK_fieldk1 asc, PK_fieldk2 asc, ..,PK_fieldkn asc

I am using this simulating limit(beging,records) from mysql.

0
 
dbeneitCommented:
if we want to show records from 50000 to 51000
1.- select first 51000 records in order
2.- select last 1000 records in the oposite order
3.- we put records in right order
0
 
Anthony PerkinsCommented:
>>Is there a difference between creating the #temp table or using table datatype variables (I AM using SQL Server 2000)?<<
In general variables of type table will always be a better choice than temporary tables.  There are exceptions, however. I always use variables of type table unless it is syntactically not supported.  For one you don't have to worry about dropping them :)

>>Is the temp table method even better than the cursor method?<<
Most definitely.  Cursors should only be used as a last resort. I don't recall the last time I used them in  a Production environment.  I think it was with SQL Server 6.5 :)

>>Am I right about the nested TOP queries needing a sequential PK in the results?<<
I believe so, but it has been a while since I used them.  My main gripe with them is that you cannot dynamically set the order on any column and any direction.  You can only do this with:
1. (maybe) Cursors, but you will get horrendous performance or
2. Temporary tables or variables of type table.

For me this is a no brainer.  But perhpas that is because I use this method in 80% of my stored procedures and all the ones that require paging.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now