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!