How to implement paging in an effective way?

Question

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

Answer 1

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.

Answer 2

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.