Paging in ASP and ASP.Net

This article Shows & compares many paging methods of ASP and ASP.Net and also SQL Paging methods that can be used in both of them..
You would notice here that all the fast paging methods runs a small query first to get number of records and calculate page count..

Classic ASP
in ASP , The article [How do I page through a recordset?] is a very good refrence. and I think that paging by Recordset.Move() was the best of paging methods that don't use stored procedures.

Even Recordset.GetRows() + Recordset.Move() performed best -as the article says- which combines the effective move() technique, with GetRows(). but for me since I use Custom Business Classes ,GetRows() is not needed.

GetRows() converts a heavy recordset object into a lighter-weight array for local processing

Recordset.Move()
The Recordset.Move() technique uses Move() method to skip the first n rows in the result set to start at the first row for the page we are interested in.

<!--#include file=inc.asp-->
<!--#include file=topRS.asp-->
<%
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 "<table border=0 cellpadding=5>"
for x = 1 to PerPage
if rs.eof then exit for
artist = rs(0)
title = rs(1)

rs.movenext
next
response.write "</table>"
else
response.write "No rows found."
response.end
end if
%>
<!--#include file=foot.asp-->

ASP.Net
In ASP.Net you would hear about DataGrid but it is terrible cause each time you move another page the entire records are fetched..
Now lets examine good paging methods for ASP.Net:

1) Dataset
Using the method DbDataAdapter.Fill(DataSet, Int32, Int32, String) which specifies the start record and number of records to fill the DataSet with.

'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 ] Says:

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.

That is true, At first testing the time results for this method was bad even for closer pages..
To tweak this: I added a top clause in the select query to select Top(PageSize * PageIndex), so if we are requesting the 2nd page of a page size of 10 the database returns only 20 records, and the DataSet is filled with the 2nd 10 records.. instead of letting the database to return the entire table (1 million records). this tweak which i used for DataSet,DataReader and Recordset make their performance+time is much better for closer pages.

2) DataReader
You won't see this out there very often! it is inspired By ADO Paging method[Recordset.Move()]. check it out:

'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

When it is only one table you query or it is read only & forward only mode : DataReader is better.
and According to tests @ [A Speed Freak's Guide to Retrieving Data in ADO.NET] DataReader will be noticeably faster in a larger Page Size

3) Recordset
What? yes, why not.. lets try ADO Recordset in ASP.Net, just add reference to Microsoft ActiveX Data Objects 2.5+ ,here is the code:

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 2 methods will use SQL Paging which can be used in Both ASP and ASP.Net...

4) Top Clause
A SQL Paging using Top Clause as described in MSDN [How To: Page Records in .NET Applications] , and Reading records using a DataReader.

'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

Note that: If you are at last page and number of records their is less than PageSize, This method will always return the last PageSize records.. so you would need to skip some records first to reach the desired records..

5) Row_Number Function
A SQL Paging method described in [Custom Paging in ASP.NET 2.0 with SQL Server 2005], Row_Number is a new method introduced in SQL Server 2005, which enables us to associate a sequentially-increasing row number for the results returned.

'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

Interesting results on Timing these Paging methods
I Timed these paging methods on one table of more than 1 million records , getting & looping 100 records per page , and page index moves further to get records as shown in the table.
Tests made using [Microsoft Web Stress Tool] on a PC of P3.2GHZ, 1GB Ram, Windows XP, SQL Server 2005 Express.

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

DataReader,DataSet and Recorset methods time is close to 'Top Clause' method after using the tweak menthioned before, but DataReader gives slightly better time than DataSet.

The old ADO Recordset is not bad, but you should not need it in ASP.Net.

SQL Top Clause is slightly faster, although the SQL Server resources usage is not so efficient in further pages.

Row_Number is the fastest if you have SQL Server 2005.

* Generally, when the SQL query sort or search records by indexed columns: query cost is lower..

Update!: I wrote a VB.Net Class to encapsulate all that Data Access code to help me [Write Less & Generic Data Access Code in ADO.NET 2.0], check it out!

Tags: ADO , ADO.Net , ASP , ASP.Net , DAL , Paging , performance , SQL , SQL Server