Data Pagination: SQL Server 2005 + SL3

Question

Hey everybody, got an interesting question I think. I've got a Silverlight3 application which is querying a SQL Server 2005 database asynchronously. However, some of the data sets the queries return are massive and so I'm looking into data pagination.

Goals:

1) Just in time data calls - I only want to query for page 3's data when the user clicks to go to page 3.

2) I want a slider to control which page I'm on - SliderControl is SL3 with its movement binded to a stored procedure call (is my initial guess as to an approach).

3) Read ahead data as a label for the slider. So the slider will say "Page 1 of 50" or perhaps "Gant - Hart". Some sort of indication as to where you are in the data without actually querying for all the data until the user drops the slider into position.

I'm almost positive that I need to do some sort of a query to get the total number of rows and some sort of bookmark data that the query will eventually return. Otherwise I don't know how to split up the slider nor would I be able to do page labels (The gant to hart stuff).

Anyone have experience with this kind of thing? Thanks.

Answer 1

There are usually at least 2 separate lookups involved here, the first to get the pager information and a second to retrieve the data appropriate to the current page.

The pager (slider) needs to know the total number results that would be returned if you weren't doing paging. This is needed to show the pretty "Total # of Results" indicator for your use and to calculate how many total pages you've got. In your case you're going to want to format a nice visual display of the pages for selection (determined by your desired records/page value) in your slider. That's your first query; it should return just a single scalar int or long and you can calculate what you need for your slider display.

The second results include the actual records to be displayed for a given page.

Step 1 of 2 for a page of data is to run a query to filter and order your results by a primary key present within the data you're returning, throwing the key values in a temp table with an AUTO_INCREMENT/IDENTITY field or getting the row number over for the results of a derived table. (Summary: 2 fields, 1 for sequence/1 for primary key to join in step 2).

Step 2 of 2 is to join the key values with your tables which contain data, ordering by the sequence determined in Step 1, selecting only rows numbered (desired start row) to (desired end row) as determined by the page number and page size you've selected.

A stored procedure is strongly advised to keep the data on the server during this operation while you're having to examine more data than you really care to bring back to optimize bandwidth.