Implementation of Custom Data Paging - (for SQL Server 2000)
Tags : Data, retrieval, Database, Paging, Performance
One of the most common operations encountered in data driven applications is the displaying data in a tabular manner. When the amount of data being displayed is small, all the data can be displayed in a single list. However, for large amount of data, applications (especially web-applications) often resort to using a paged tabular display.
Paging of data to be displayed can be done either at the UI level or at the Database/Data Layer. In this article, we will explore some of the possible methods of implementing data paging at the database level for SQL Server 2000.
Retrieve all the data from the database and page at the UI Layer
PROS
- Easier and faster to implement in cases where the results can directly be linked to the display component as in the case of a binding a dataset to a data-grid control in ASP.NET. This approach also suffices where as the amount of data being handled and displayed is small and the database server and web-server are hosted on the same physical machine.
- Other aggregation operations such as total/average can be performed directly and with minimal effort on the results since all the data needed is present in the set of results.
CONS
- The loading time of data is directly proportional to the number of records in the Database at any point. This means that as the data in the system increases (which is almost always the case), the system response time degrades.
- In the case of a multi-server deployment, where the database and web servers are separate, a large overhead is incurred for transferring unnecessary data across.
Retrieve only the page to be displayed currently (effectively paging at the Database Layer)
PROS
- The loading time of the page is minimally effected by the amount of data in the database. With increase of size of the database over time, the system will only be slightly affected, keeping the overall user-experience good.
- Different server deployment scenarios do not affect this approach as the amount of data being transferred between the database and web servers is constant (assuming the page size is constant).
CONS
- Implementing this with SQL Server could be more time-consuming
- There is not yet any single or generic way in which this can be implemented easily for SQL Server 2000. The choice of approach will depend to a large extent on the structure of the table and the primary keys as well as the query being used to retrieve the data
- For architectures where stored procedures are not used but SQL queries executed directly at the Data Access Layer, implementing this will need to be done on a case by case approach.
Test Data
For testing purposes we will be using a test database with a single table Customers.The structure of the table is:
The primary key CustomerID is an auto-increment field and is indexed
Paging Approaches
METHOD 1
STEP 1: Create a temporary table with the fields that make up the primary key (i.e. uniquely identify each row in the result set) of the SELECT query being executed. An extra field ID which is auto-incremented is also created.
STEP 2: Execute the query and insert the primary key columns into the temporary table
STEP 3: Run the query selecting all the necessary result columns while JOINING the necessary tables with the temp table and limiting the results by using the ID column
A sample procedure for the Customer table specified previously is
Some important points to keep in mind are
- In STEP 2, the ROWCOUNT is first set to the end row number. This is a tweak to minimize the number of rows inserted into the temp table and improve execution time.
- In STEP 3, the ROWCOUNT is set to the page-size. This again restricts the number of records retrieved thereby improving performance.
METHOD 2
This method makes use of the TOP keyword in SQL-Server combined with a LEFT JOIN to get the desired set of records
METHOD 3
This method uses TOP combined with an inner query to get the desired set of records.
Performance Comparison
The results of testing each of the above mentioned methods against a table with 1 million customer records, using a page-size of 10 records gave the following results.
Some related links for improving and measuring performance of queries and stored procedures in SQL Server 2000 are listed below:
http://www.sql-server-performance.com/stored_procedures.asp
http://www.agsrhichome.bnl.gov/Controls/doc/database/opt_query.html
Send your comments to Rohit.
