Paging in ASP and ASP.Net refers to the process of dividing a large set of data into smaller, more manageable chunks for display on a web page

This article explores and compares various paging techniques in ASP and ASP.NET, including SQL-based methods applicable to both. A key observation is that efficient paging methods often execute a quick query upfront to determine the total record count for page calculation.

Classic ASP

The article “How do I page through a recordset?” serves as an excellent resource for ASP paging. Paging with Recordset.Move() is particularly effective for methods that don’t rely on stored procedures.

Although the article highlights Recordset.GetRows() combined with Recordset.Move() as the top performer—converting a resource-intensive recordset into a lightweight array—using custom business classes often negates the need for GetRows().

Recordset.Move()

This technique utilizes the Move() method to bypass the initial ’n’ rows of the result set, directly accessing the desired page’s first row.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
<%  
rstart = PerPage * Pagenum - (PerPage - 1)  
dataSQL = "SELECT ArtistName, Title FROM SampleCDs WITH (NOLOCK)"  
set rs = conn.execute(dataSQL)  
if not rs.eof then  
rs.move(rstart-1)  
response.write ""  
for x = 1 to PerPage  
if rs.eof then exit for  
artist = rs(0)  
title = rs(1)  
  
rs.movenext  
next  
response.write "

"  
else  
response.write "No rows found."  
response.end  
end if  
%>  

ASP.NET

While ASP.NET offers the DataGrid, its performance suffers as it retrieves all records with each page change. Let’s explore better alternatives:

1) Dataset

This method utilizes DbDataAdapter.Fill(DataSet, Int32, Int32, String) to populate the DataSet with a specific range of records.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
'Count Query
Cmd.CommandText = "Select count(ID) from Users where.."
Count = CInt(Cmd.ExecuteScalar())
PageCount = Math.Ceiling(Count / PageSize)

'Select Query
cmd.CommandText = "Select Top " & (PageIndex * PageSize) & " * from Users where.. Order By.."

Dim DS As New DataSet
Dim DA As DbDataAdapter = PFactory.CreateDataAdapter()
DA.SelectCommand = Cmd
DA.Fill(DS, (PageIndex - 1) * PageSize, PageSize, SrcTable)
Dim DT As New DataTable= DS.Tables(SrcTable)
DA = Nothing
DS = nothing

For Each Row As DataRow In DT.Rows
   'Do Something
Next
DT = Nothing

However, MSDN (ADO.Net & Paging Through a Query Result) notes:

This might not be the best choice for paging through large query results because, although the DataAdapter fills the target DataTable or DataSet with only the requested records, the resources to return the entire query are still used .. Remember that the database server returns the entire query results even though only one page of records is added to the DataSet.

Indeed, initial tests revealed slow performance with this method, even for nearby pages.

Optimization: Adding a TOP clause to the SELECT query limits the returned records. For instance, requesting the second page with a page size of 10 retrieves only the first 20 records, filling the DataSet with the desired second 10 records instead of the entire table. This enhancement, applicable to DataSet, DataReader, and Recordset, significantly improves performance for closer pages.

2) DataReader

Inspired by ADO’s Recordset.Move() paging, this approach might not be as common but proves effective.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
'Count Query
Cmd.CommandText = "Select count(ID) from Users where.."
Count = CInt(Cmd.ExecuteScalar())
PageCount = Math.Ceiling(Count / PageSize)

'Select Query
cmd.CommandText = "Select Top " & (PageIndex * PageSize) & " * from Users where.. Order By.."

Dim Reader As DbDataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection)

'> Move to desired Record
Dim startRecord As Integer = (PageIndex - 1) * PageSize
For i As Integer=0 To (startRecord - 1)
   If Not Reader.Read Then Exit For
Next

While Reader.Read()
   'Do Something
End While
Reader.Close()
Reader = Nothing

DataReader excels when querying a single table or dealing with read-only, forward-only scenarios. Tests conducted in “A Speed Freak’s Guide to Retrieving Data in ADO.NET” demonstrate its noticeable speed advantage with larger page sizes.

3) Recordset

Utilizing ADO Recordset in ASP.NET might seem unusual but is achievable by referencing “Microsoft ActiveX Data Objects 2.5+”.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Dim Conn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Conn.Open(ConnString)
Cmd.ActiveConnection = Conn
Dim RS As New ADODB.Recordset
Dim i As Integer = 0, Count As Integer = 0

'Count Query
cmd.CommandText = "Select count(ID) from Users where.."
Rs.Open(Cmd, , CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly,CommandTypeEnum.adCmdText)
If Not Rs.EOF Then Count = CInt( Rs(0).Value)
Rs.Close()
PageCount = Math.Ceiling(Count / PageSize)

'Select Query
cmd.CommandText = "Select Top " & (PageIndex * PageSize) & " * from Users where.. Order By.."
Rs.MaxRecords = PageIndex * PageSize
RS.Open(Cmd, , CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly,CommandTypeEnum.adCmdText)
If Not RS.EOF Then RS.Move((PageIndex - 1) * PageSize)

While not RS.EOF()
   'Do Something
   Rs.MoveNext()
Wend
RS.Close()
RS = Nothing
Conn.Close()

SQL Paging

The following methods leverage SQL for paging, suitable for both ASP and ASP.NET.

4) Top Clause

This technique, described in MSDN’s “How To: Page Records in .NET Applications,” utilizes the TOP clause and a DataReader for retrieval.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
'Count Query
Cmd.CommandText = "Select Count(ID) from Users where.."
Count = CInt(Cmd.ExecuteScalar())
PageCount = Math.Ceiling(Count / PageSize)

'Select Query
Cmd.CommandText= "Select * from (" & _
   "Select Top " & PageSize & " * from (" & _
   "Select Top " & (PageIndex * PageSize) & " * from Users as T1 where.." &_
   " Order by ID asc " & _
   ") as T2 Order by ID desc " & _
   ") as T3 Order by ID asc "

Dim Reader As DbDataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection)

'Last page fix!!
If PageSize>1 And PageCount>1 And PageIndex=PageCount And (Count Mod PageSize<>0) Then
   For i =1 To PageSize - (Count Mod PageSize)
      If Not Reader.Read Then Exit For
   Next
End If

'Loop the desired records
while Reader.Read()
   'do Something
end While
Reader.Close()
Reader = Nothing

Caveat: On the last page, if the record count is less than the PageSize, this method always returns the last PageSize records. Skipping records might be necessary to reach the desired data.

5) Row_Number Function

Introduced in SQL Server 2005 and detailed in “Custom Paging in ASP.NET 2.0 with SQL Server 2005,” the ROW_NUMBER() function assigns sequential numbers to query results, facilitating paging.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
'Count Query
Cmd.CommandText = "Select count(ID) from Users where.."
Count = CInt(Cmd.ExecuteScalar())
PageCount = Math.Ceiling(Count / PageSize)

'Select Query
Cmd.CommandText="Select Top " & PageSize & " * from (" & _
   "Select *,ROW_NUMBER() OVER (ORDER BY ID ASC) AS Row from Users where.."
   " ) as T1 where Row>" & ((PageIndex - 1) * PageSize) & " and Row<=" & (PageIndex * PageSize)

Dim Reader As DbDataReader= Cmd.ExecuteReader(CommandBehavior.CloseConnection)
while Reader.Read()
   'do Something
end While
Reader.Close()
Reader = Nothing

Performance Comparison

Benchmarking these methods on a table exceeding one million records, retrieving 100 records per page and progressively fetching further pages, yielded interesting results. The test environment: P3.2GHZ CPU, 1GB RAM, Windows XP, SQL Server 2005 Express, and the Microsoft Web Stress Tool.

Method	1 to 10000	10000 to 100000	100000 to 500000	500000 to 1000000
Recordset	601.00	1101.06	2708.94	4750.35
DataSet	518.79	734.94	2264.78	4463.53
DataReader	490.12	813.29	2165.71	4094.18
Top Clause	518.88	735.29	1881.18	4017.88
Row_Number	381.18	466.35	801.18	1309.76

The TOP clause optimization brings the performance of DataReader, DataSet, and Recordset close to the dedicated Top Clause method, with DataReader slightly outperforming DataSet.
While functional, the legacy ADO Recordset might not be the optimal choice in ASP.NET.
The SQL TOP clause exhibits slightly faster speeds, though SQL Server resource utilization becomes less efficient with distant pages.
ROW_NUMBER() emerges as the clear winner for SQL Server 2005 and above.

General Observation: Sorting and searching by indexed columns significantly reduce SQL query costs.

Update: A VB.NET class encapsulating these data access techniques to streamline coding is available in “Write Less & Generic Data Access Code in ADO.NET 2.0.”