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.NetIn 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) DatasetUsing 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 = NothingHowever, 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 = NothingWhen 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 PagingThe following 2 methods will use SQL Paging which can be used in Both ASP and ASP.Net...
4) Top ClauseA 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 = NothingNote 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 FunctionA 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 = NothingInteresting results on Timing these Paging methodsI 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!
Few months ago I posted a Javascript class on how to Implement a paging listbox using jQuery. which has drawn a lot of traffic and few questions lately so I thought it would be more convenient to rewrite the code as jQuery plugin and make few enhancements plus providing a complete sample code in VB.Net and C#.
Web developer, jQuery plugin author, social media fan and Technology Blogger.