I have often encountered the culture of query for thousands of results, estimate the paging and then only display a small segment of the results that were retrieved from the database. This is particularly bad in a web environment. This results in the following issues:
- A large amount of data to be transferred from the database to the website
- Longer than necessary database queries
- Script Errors and timeouts
- Frustrated users/customers
The better approach is to allow the database to fetch only the results that are required for paging. Here is an example of what a query may look like.
Declare @PageNumber int Declare @PageSize int --Assume we need page 6 i.e. records from 51-60 Set @PageNumber = 6 Set @PageSize = 10
Select Top(@PageSize) * from ( Select rowNumber=ROW_NUMBER() OVER (ORDER BY descriptionOfGoods), productID,descriptionOfGoods,itemNumber,MSRPrice,listPrice, totalRows=Count(*) OVER() –-Count all records from Products ) A Where A.rowNumber > ((@PageNumber-1)*@PageSize)
Note: This works best when indexes are up-to-date and configured properly. While this seems obvious, it is also a step that I often see missed.