|
Introduction
Presenting a large set of data in readable pages is a standard task for Web- application developers. Applications that need to produce search results in a digestible manner need to apply paging algorithms. This article will compare the three ways in which paging can be achieved for an ASP/SQL Server 7 application. The first uses ActiveX Data Object (ADO) paging; the second uses getrows and an array; and the third uses a SQL Server 7 stored procedure. Of those, the third is the most efficient because it returns the minimum set of data from the database server to the Web server.
Solution 1: Paging via ADO
To use ADO's paging system, the cursor location of either the recordset or connection object must be set to adUseClient, or a client-side cursor, before the recordset is filled.
rstProjects.CursorLocation = adUseClient
Next, execute a command or query and obtain a recordset. Set the page size property of the recordset to the number of records that a page should show. And set the absolute page property of the recordset to the current page number. These two properties enable ADO to find and present the subset of the recordset that is relevant for the current page. Note that with these properties set, ADO will still return the entire recordset to the client; only the BOF and EOF markers (ADO properties) will be different.
To maintain state information about the current page, either use a query string or a hidden field. In either case, ensure that if the user chooses to move to the next page, the current page variable increases by one, and if the user chooses to move to the previous page, the current page variable decreases by one.
If (Len(Request.QueryString("page")) = 0) Or (IsNumeric(Request.QueryString("page")) = False) Then
intCurrentPage = 1
Else
intCurrentPage = CInt(Request.QueryString("page"))
End If
rstProjects.PageSize = 25
rstProjects.AbsolutePage = intCurrentPage
The next step is to construct the loop that will display the records for the current page. As per sound ADO practice, when looping through a recordset to display data, set up local variables that reference the various fields and then access those local variables.
If rstProjects.RecordCount > 0 Then
Dim fldProject, fldBuyer, fldBidder, fldAverageBid
Set fldProject = rstProjects.Fields("Project")
Set fldBuyer = rstProjects.Fields("Buyer")
Set fldBidder = rstProjects.Fields("Bidder")
Set fldAverageBid = rstProjects.Fields("AverageBid")
Dim intCurrentRecord
intCurrentRecord = 1
Do
Response.Write("<p>" & fldProject.Value & "</p>" & vbCrLf)
Response.Write("<p>" & fldBuyer.Value & "</p>" & vbCrLf)
Response.Write("<p>" & fldBidder.Value & "</p>" & vbCrLf)
Response.Write("<p>" & fldAverageBid.Value & "</p>" & vbCrLf)
rstProjects.MoveNext
intCurrentRecord = intCurrentRecord + 1
If rstProjects.EOF Then Exit Do
Loop While intCurrentRecord < rstProjects.PageSize
Any kind of loop could achieve the same effect, but I've presented a fairly verbose Do-Loop-While loop for heuristic purposes.
Solution 2: Paging via getrows and an Array
The getrows approach will mimic the same algorithm as ADO paging but will use an array instead of an ADO recordset. By putting all the data into an array shortly after the recordset is filled, the recordset object can be closed and set to nothing, thereby freeing up system resources. As a result, this approach should be more efficient that the ADO approach.
If rstProjects.RecordCount > 0 Then
Dim arrProjects
arrProjects = rstProjects.GetRows
rstProjects.Close
Set rstProjects = Nothing
Dim intTotalRecords
intTotalRecords = UBound(arrProjects,2)
Dim intPageSize
intPageSize = 25
Dim intStart
intStart = ((intCurrentPage - 1) * intPageSize)
Dim intEnd
intEnd = intStart + intPageSize - 1
Dim fldProject, fldBuyer, fldBidder, fldAverageBid
Dim intCurrentRecord
intCurrentRecord = intStart
Do
fldProject = arrProjects(0, intCurrentRecord)
fldBuyer = arrProjects(1, intCurrentRecord)
fldBidder = arrProjects(2, intCurrentRecord)
fldAverageBid = arrProjects(3, intCurrentRecord)
Response.Write("<p>" & fldProject & "</p>" & vbCrLf)
Response.Write("<p>" & fldBuyer & "</p>" & vbCrLf)
Response.Write("<p>" & fldBidder & "</p>" & vbCrLf)
Response.Write("<p>" & fldAverageBid & "</p>" & vbCrLf)
intCurrentRecord = intCurrentRecord + 1
If intCurrentRecord >= intTotalRecords Then Exit Do
Loop While intCurrentRecord < intEnd
When using getrows, avoid misusing the two-dimensional array built by getrows. In particular, it is not uncommon to mistake a column when setting up the fields to display.
Solution 3: Paging via a SQL Server 7 Stored Procedure
The third and final approach involves a stored procedure. This is the most efficient approach because, unlike ADO and getrows which both return the entire set of records to the Web server, the stored procedure returns only the records that are needed for the current page.
Once again, the paging algorithm is roughly the same as ADO, but here the current page and the page size are passed into the stored procedure as input parameters. The stored procedure then selects the set of records needed for the current page by setting up a temporary table with an identity field and using the identity field to determine which records should be returned, given the current page and page size.
CREATE PROCEDURE "sprocInformationTechnologyProjects"
@Page int,
@Size int
AS
DECLARE @Start int, @End int
BEGIN TRANSACTION GetDataSet
SET @Start = (((@Page - 1) * @Size) + 1)
IF @@ERROR <> 0
GOTO ErrorHandler
SET @End = (@Start + @Size - 1)
IF @@ERROR <> 0
GOTO ErrorHandler
CREATE TABLE #TemporaryTable
(
Row int IDENTITY(1,1) PRIMARY KEY,
Project varchar(100),
Buyer int,
Bidder int,
AverageBid money
)
IF @@ERROR <> 0
GOTO ErrorHandler
INSERT INTO #TemporaryTable
SELECT ...
// Any kind of select statement is possible with however many joins
// as long as the data selected can fit into the temporary table.
IF @@ERROR <> 0
GOTO ErrorHandler
SELECT Project, Buyer, Bidder, AverageBid
FROM #TemporaryTable
WHERE (Row >= @Start) AND (Row <= @End)
IF @@ERROR <> 0
GOTO ErrorHandler
DROP TABLE #TemporaryTable
COMMIT TRANSACTION GetDataSet
RETURN 0
ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN @@ERROR
With SQL Server 2000, the stored procedure for paging described above can be enhanced by using a local table and a "user-defined function."
Conclusion
In summary, programming within a stored procedure is always superior because network traffic is kept to a minimum. Even with ASP.NET's DataGrid with in-built paging around the corner, the stored procedure approach to paging is the best when dealing with large sets of data.
About the Author
Stephen Lian is the principal of Active Data Online Pty Ltd, http://www.activedataonline.com.au.
|
