Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SirRawlins
Starting Member
14 Posts |
 Posted - 2011-10-25 : 09:32:31
|
| Hello Guys,I'm looking for some advice on the most efficient way to query for the details required for a paginated result set.I need both:1) The records for the particular page.2) The total number of records in the table.This would allow me to display something like 'Showing 20 records of 2400' or something to that effect.My question is, should I run this as two separate queries? one for the records and another for the total count?I only ask as my thoughts are that if running a query for SELECT COUNT(some_id)... is just as weighty as doing SELECT Some_ID... then I may as well select all the records from the table, and paginate them at an application level rather than at the SQL level, as it'll be half as many queries.If however there is a great performance difference in the two, then keeping them as separate queries and paginating at the SQL level makes more sense.If anyone has any questions then please let me know.Robert |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-10-25 : 11:02:16
|
| All the places I've worked application side paging would be out of the question. There was waaay too much data on the sever to make that practical. So the answer to your question depends on some factors you didn't provide. The first thing I suggest you do is search this site for "paging" as there are a lot of methodologies discussed. To provide the "most efficient way" you will need to provide some more details as to your requirements such as: is your data always changing (OLTP system) or is the data static? Can each page request reflect the current data or should the entire result set (all pages) be cast at the time the request is sent?Will you provide multiple sort options?Is each search basically the contents of a table or are these more complex, parametrized searches across many tables (varying based on search parameters)?Be One with the OptimizerTG |
 |
|
|
paultech
Yak Posting Veteran
79 Posts |
Posted - 2011-10-25 : 18:56:47
|
| Hello ,As i thought that you need something like Crystal Report or Sql Report => that will apply all of your requirmentspaul Tech |
|
|
|
|
|
|
Query for Pagination