Record Paging in SQL Server
One of the most common problems during a development of a database application is displaying large amounts of data. Fetching all of the data from your server and displaying it directly in your application is not a very good idea. Coz fetching large amounts of data could clog your server or at worse makes your application non-responsive.
A more common way of solving this problem is letting the user query the data first before displaying it. This would minimize the data being displayed and hence would lessen the memory consumption of your application. But what if the user really needs to browse all of the data in your table(s)? A very efficient way to solve this is by paging your data. Paging of data works in a way that you limit the view of your user. A perfect example of data paging is the Google search engine. Once you issue a query into the database only a limited SET of data is being returned per view. You can the move to the other page to see the next results.
In the VB6 days this is done quite easily, coz the recordset object directly supports paging through its CacheSize, PageSize and PageCount properties.
As you may have now noticed, ADO .Net is very different with its predecessor (ADO). Since the loading of your data greatly depends on the interaction between DataAdapters and Datatables the ability of data paging should be done before the DataAdapter loads your data in the Datatable. Which gives you no choice but to use sql server side code to do this.
I have a friend that is an avid user of the mysql database. And I found out through him that you can do data paging through sql-scripts in mysql. But sadly, I can’t seem to find its equivalence in Sql Server.
Ok, don’t give up your hope on sql server and jump directly to mysql for I have found a workaround for this.
Before discussing the methods used to achieve data paging through sql server. I must say first that this is just my idea. This method doesn’t indicate that it’s the only way to achieve this. I’ve not yet tested it on very large databases, though I’ve tested it partially.
Let’s start then.
In the MSDN forums, a lot of users have been asking this question. And most of their answers suggests to use the TOP keyword in sql server. Well, its not entirely effective since the top keyword only returns the first number of records that you wish to query. After that, you wouldn’t be able to get the next SET.
To be able to get the next set we must have a record counter or a way numbering our records. You can’t achieve that by using an auto increment field coz this field would not be in order when deletions are made in your table. The numbering should occur upon querying the data. Here’s a solution that I’ve come up with:
--Create a View
SELECT TOP (100) PERCENT COUNT(*) AS recnum, a.CustomerID, a.TerritoryID
FROM Sales.Customer AS a INNER JOIN
Sales.Customer AS b ON a.CustomerID >= b.CustomerID
GROUP BY a.CustomerID, a.TerritoryID
ORDER BY a.CustomerID
-- You can now select your data this way:
SELECT * FROM vw_test WHERE recnum BETWEEN 100 AND 110
Upon testing this, I noticed that it was a little bit slow. It executed for 1:15! By the way the database that I used this query to is the Answerworks database. The table contains 19,000+ records.
I know that the sql script was not that efficient. So I tried another way using a stored procedure. I numbered my records by creating a temporary table with an autonumber field. Here’s the script:
CREATE PROCEDURE sp_test
@start int,
@end int
AS
BEGIN
DECLARE @tmpTable TABLE (
recnum int identity,
CustomerId int,
TerritoryId int
)
INSERT INTO @tmpTable(CustomerId, TerritoryId)
SELECT CustomerId, TerritoryId FROM Sales.Customer
SELECT * FROM @tmpTable WHERE recnum BETWEEN @start and @end
ORDER BY recnum
END
GO
-- You can use it this way:
EXECUTE sp_test 100, 110
GO
To my surprise it executed only at 25 seconds. Although it’s a little bit slow but at least it has greatly improved its performance compared to my view approach.
I guess you have the final say on what approach you are going to use. But as you choose, take into consideration the length of the code that you are going to do vs. the number of records that you are trying to process.
Either you choose method 1 or 2 the results of these could then be loaded into your datatable. You can then just change the limits (start, end) of your parameter to get the pages that you want your user to view to ease the burden from your application.