Paging through data is a common requirement in distributed applications. For example, the user might be presented with a list of books where it would be prohibitive to display the entire list at once. The user will want to perform familiar activities on the data, such as viewing the next or previous page of data or jumping to the beginning or end of the list.
This section discusses options for implementing this functionality, and the effect of each option on scalability and performance.
Comparing the Options
The options for data paging are:
- Using the Fill method of the SqlDataAdapter to fill a DataSet with a range of results from a query
- Using ADO through COM interoperability and use a server-side cursor
- Using stored procedures to implement data paging manually
The best option for paging your data is dependent on the factors listed below:
- Scalability requirements
- Performance requirements
- Network bandwidth
- Database server memory and power
- Middle-tier server memory and power
- Number of rows returned by a query you want to page
- Size of your data pages
Performance tests have shown that the manual approach using stored procedures offers the best performance across a wide range of stress levels. However, as the manual approach performs its work on the server, server stress levels can become a significant issue if a large proportion of your site's functionality relies upon data paging functionality. To ensure that this approach suits your particular environment, you should test all options against your specific requirements.
The various options are discussed below.
Using the Fill Method of SqlDataAdapter
As previously discussed, the SqlDataAdapter is used to fill a DataSet with data from a database. One of the overloaded Fill methods (shown in the following code) takes two integer index values.
public int Fill(
The startRecord value indicates the zero-based index of the start record. The maxRecords value indicates the number of records, starting from startRecord, to copy into the new DataSet.
Internally, the SqlDataAdapter uses a SqlDataReader to execute the query and return the results. The SqlDataAdapter reads the results and creates a DataSet based on the data read from the SqlDataReader. The SqlDataAdapter copies all of the results through startRecord and maxRecords into a newly generated DataSet and discards the results that it doesn't need. This means that a lot of unnecessary data could potentially be pulled across the network to the data access client, which is the primary drawback to this approach.
For example, if you have 1,000 records and want records 900 through 950, the first 899 records are still pulled across the network and discarded on the client side. This overhead would probably be minimal for small result sets, but could be significant when you page through larger sets of data.