Server Side Paging
A new feature being released (sadly, one of the very few TSQL Enhancements) in SQL Server Denali is the ability to do server side paging.
You have a web page allowing you to display a limited amount of rows. It calls a database query that may produce many more times the number of rows presented. This is a common problem we face in database systems.
The problem is more complicated when you add sorting to the equation. Consider the situation when you have a query returning 1,000 rows with the filters provided by the user, yet they have the ability to sort as well. Now you wish to return only the top N rows which may be presented in their browser. If you return all of the 1,000 rows before sorting it may take quite some time before getting the final data to the requesting application.
Frequently it is solved using a ROW_NUMBER function to order the results, and allow you to return N number of records between Start and Finish of the requested page. This means that with every new page request, you must apply the filter, sort the results, and then extract the rows for the desired page.
MySQL has simplified this whole process by allowing you to specify the page you wish and the number of rows per page, and simply returns the desired rows. Of course it still filters and sorts. Denali now has a new feature allowing the same syntax simplification.
I say, so what! There was a good reason not to put it in to start with. It doesn’t belong in your database. There are a lot more useful features missing in TSQL that would have been of more value. This is about as valuable as databinding.
What is missing here? The fact that your web server is talking to your database in the first place is a design decision that forces you to utilize your most precious (and expensive to scale) shared resource; your database server. Microsoft is great at putting together frameworks. Here is a framework that is desperately needed in the developer community…caching.
What should really be happening here is the ability to return data into business objects. If there is truly sooo much data, then go ahead and filter on the server. But, return the data to a middle tier where it can be sorted, cached, and paged EASILY through array pointers. You can even have it lazy load so that it returns the first page to the cache, returns that first page back to the requester, and continues to load the cache. You don’t have to load the entire thing, but why not get ten pages of output (that the user probably won’t request). Even if you did all the filtering and sorting on the database, with this model you reduce the load on your database server by a factor of 10.
Putting a caching framework into your application will take a little work, but should be easy to use, especially now with Dot Net generics.
Jumping down solidly off my soapbox, let me say that I continue to code SQL Server paging. I haven’t found or put together a caching framework; I have too many other fish to fry. In a perfect world, one that I plan on working on real soon, there will be a caching framework, and we can take the load of our database and push it out to the consumer where it belongs. Until that time, Denali will simplify our paging syntax. But don’t be deceived, simply because the syntax is easier, I can’t see how it will be less of a load on your database server.
Would you like to weigh in on this discussion? I really value your insight and contributions. Send your thoughts on filtering, caching, and paging as well as your experience or frameworks you have used. Let’s send some hope to the masses. Drop your comments by Email to btaylor@sswug.org.
Cheers,
Ben