Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database query which could result in a big result set. The client which displays the data receives the data over network, so the idea was to minimize the amount of transferred data by retrieving only the first 50 results from the database and sending them over to the client. Then I will provide a possibility to jump to the second page to retrieve the next 50 results etc. (something similar what e.g. google offers)

The question is what is the effective way of implementing paging. I want to make sure that mssql uses cache as much as possible and that the same is not executed once again everytime I change the paging.

There are more clients who are querying the database in the same time. Used sql engine: MS SQL 2005

My ideas were:

  • Use prepared sql statemenst to ensure execution plan sharing
  • use ROW_COUNT variable to retrieve only the needed rows

But is it really the most effective way? Or do you think that it would be better to retrieve the whole result set and implement paging in the code which sends the data over to the client?

Thank you for your tips!

Regards, Tomas

share|improve this question

2 Answers 2

up vote 5 down vote accepted

The query will be executed every time. SQL Server does not cache results.

Proper paging won't be implemented until SQL Server 2011, but until then your options (as you identified) are:

  • ROW_NUMBER() and queries on demand
  • client caching

A prepared SQL statement will be executed every time too.

If you have a fat client then caching is local to the client. This is mostly OK unless you have millions of blobs for example.

In our web client we render all the results but only show the top 100 and have a "Show all" button than expands a hidden DIV with rows 101+. We don't cache in the web server and we don't offer paging.

share|improve this answer
    
Thanks. And what is better approach if the user changes some sorting criteria? Should I execute the query once more with the new criteria or sort the results on the client? –  Tomas Walek Mar 3 '11 at 15:19
1  
I would sort in the client. The data is there already. Yes, a database is good at sorting but if the data is the same and available why make another call? We use a jQuery add-on to enable sorting in the browser :-) –  gbn Mar 3 '11 at 15:59

It depends on your environment. I would setup a test using both methods and see which works best for you. Personally, I would page on the server. Less data over the wire and less data in the client's RAM, the better. If you can control the client machine specs, all traffic is over a nonsaturated LAN and clients always page through multiple pages quickly, then you might want to page at the client.

share|improve this answer
    
Thanks. I actually need both: the communication between server and client is implemented in WCF, so there is a maximal size for a message and if the resulting set exceeds this size, I must send more messages to transfer the complete data to the client. The clients also likes to page through multiple pages quickly... :( –  Tomas Walek Mar 4 '11 at 8:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.