Wacdesigns

I’ve been searching on the internet for several ways to perform paging on a large result set from an SQL Query, there are several solutions, that can be found some are custom and others are already provided with ASP.NET Controls. This is the solution i have been using for performing paging of large datasets for data that is being used on a current web 2.0 site.

Here is a common problem:
A website offering search capabilities for it’s users is having performance issues while getting the result from it’s database, they have more than 100K record, and users are able to perform criteria search or just a text search on it. Criteria search can take up a lot of time to process depending on how it has been implemented. Statistics have shown that the average result count is about 1000 record for text search and 500 for criteria search.

The Stored Procedure
Let’s take this SQL Query as a sample query to get result for a typical text search.

SELECT Id, ProductName, ProductDescription, ProductRating, ProductPrice
FROM tbl_Product
WHERE
ProductName LIKE '%' + @Query + '%'
OR
ProductDescription LIKE '%' + @Query + '%'

This stored procedure takes one parameter a query ( @Query ), that the website visitor types in to search for something. And the query search both the Product Name and Product Description Field. This may result in some huge result set for some key terms.

We need to optimize this query to be able to perform custom paging on the other side.

here is how the optimized code would be:

--Will calculate the number of records of this particular request
SELECT @RecordCount = COUNT(1)
FROM
tbl_Product
WHERE
ProductName LIKE '%' + @Query + '%'
OR
ProductDescription LIKE '%' + @Query + '%'

SELECT *
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Id ASC) AS Row, Id, ProductName, ProductDescription, ProductRating, ProductPrice
FROM
tbl_Product
WHERE
ProductName LIKE '%' + @Query + '%'
OR
ProductDescription LIKE '%' + @Query + '%'
) AS TmpTbl
WHERE Row BETWEEN @RowStart AND @RowEnd
ORDER BY ProductRating

I have added 2 input parameters which are the @RowStart and @RownEnd, and 1 output parameter which is @RecordCount. With these new parameters added you will be able to lauch a query based on the number of records you want to display on a particular page. If you want to display 10 records per page your input parameters will have the following values
@RowStart = 1
@RowEnd = 10
since the query is inclusive.

You can use your prefered data control to display the data, and to build up the Paging logic in ASP.NET

[ad#carousel-paging-large-result-sets-with-sql-query]