Four ways to page through records

Methods (1, 2, 3, 4) are listed in the order of ease, flexibility and common sense of implementation. We provide pros and cons for each approach, followed by commentary and example code. Keep in mind that no approach will work with every situation, but some approaches are better than others. Finally check out the conclusion at the end of the document.

Method 1: Use the magic of ADO to get the records

Pros	Quick & clean. It actually works.
Cons	Rarely, it just goes ahead and downloads all records. You are limited to Client-side cursors, thus all the processing is done on the client or in the middle-tier.

When I first saw this method, I didn't believe it. I thought that it probably only works with SQL Server. I was wrong -- it actually works with other DBs as well. The example below connects to Sybase 12 using OLEDB provider for ODBC drivers and an old Intersolv ODBC driver for Sybase 11. Yeah, you couldn't get a tougher combo, but ADO stood the test - a testament to the fine work of the ADO team, Sybase and Intersolv. All it takes is the following code:


    Dim iRecs As Long
    Dim goConn as ADODB.Connection
    Const MaxRecordsPerPage = 20
    
    'Create a connection to the nearby Sybase 12 database	
    Set goConn = New ADODB.Connection
    goConn.ConnectionString = "Provider=MSDASQL.1;Password=pwd;" & _ 
		"User ID=uid;Data Source=Dictionary"
    goConn.Open
    
    Dim oRs As ADODB.Recordset

    Set oRs = New ADODB.Recordset
    With oRs
        'make sure CursorLocation is set to adUseClient
        .CursorLocation = adUseClient
        .ActiveConnection = goConn
		
        'insert your own query here		
        .Source = "select * from ObjectParam"
		
        'The next line is the key - it tells ADO to get data in chunks of 5 records
        .CacheSize = 5    
        
        'Here we specify the number of records to show per page - 20 in this case		
        .PageSize = MaxRecordsPerPage
        .Open
        
        'Let's jump to page 40
        .AbsolutePage = 40		
        Do While Not .EOF And iRecs < MaxRecordsPerPage
            Print .Fields(0).Value;
            
            .MoveNext
            iRecs = iRecs + 1
        Loop
        Print
    End With

So, as you see, pretty standard code: you connect to DB, set up the recordset and then open it. Of interest is the iRecs variable. It basically makes sure that we will process MaxRecordsPerPage and not more. Anyway, cool all the way around. Word to the wise, I've seen several instances where ADO ignored its CacheSize property and just went ahead and got all records. Maybe I need a service pack.

Method 2: Get all records then page through them.

Pros	Easiest to program, not much processing on the database, data comes back pretty fast. Ideal if your target resultset is fairly small, not more than 1000-2000 records.
Cons	Difficult to support many users on the client as multiple records quickly conspire to take a lot of memory, requires a robust & fast network (which you should have anyway).

Before you spit in disguist, at least hear me out. I agree that ferrying 20,000 records through a tired network and then getting the necessary page is a bandwidth killer. But you have to weigh other factors when making a decision on paging technique. What is your network configuration? What is your database setup? What is the weakest link in the whole arrangement? If your network is really strong and your database is overworked, then sending ton of data across the wire may not be a big deal. It takes the processing out of the database, freeing it up to do other things. Once the data is on the client, you could relatively quickly scroll to the proper page and display the appropriate records. In addition, some data sources simply make it horribly difficult to get just the records you want. Some older databases make it downright impossible, even with the newest version of ADO.

Method 3: Use a cursor in a stored procedure to scroll and return the records you need.

Pros	The interface is pretty simple. Easy code for generators to generate (if you use those), very frugal with network bandwidth.
Cons	Definetely requires good SQL skills and deep understanding of the databases to get good performance. Cursors take a large hit on the database. If you use MySQL or Access, which don't support stored procs or cursors, you are hosed. In addition, moving between databases maybe difficult (maybe a moot issue, since it happens rarely).

This is pretty slick. However, you will need a stored proc for each table you are generating. In addition, if you are collecting data from several tables, your SQL skills will be severely tested. Below I provided a stored procedure that accepts @StartPoint and @PageSize variables, so parameters 560, 20 will return records 560 through 580. It works on a hypothetical table with various types of banking information. The SP is lengthy, if you grok SQL, it should be easy to understand.


create proc PageScroll (@StartPoint int = 1, @PageSize int = 20)
as
declare @SourceID numeric(10,0), @DelinqTyp int, @SubPool char(8), @OidSubPool char(10),
	@ServicerID char(6), @LoanNum char(20), @ProspID char(20)
declare @Counter1 int, @Counter2 int

declare LoanCursor cursor
for select SourceID, DelinqTyp, SubPool, OidSubPool, ServicerID, LoanNum, ProspID
from Loan
order by SourceID
for read only

open LoanCursor

select @Counter1 = 1

select *
into #Loan
from Loan
where 1 = 2

if @StartPoint > 1

begin
   while (@Counter1 < @StartPoint and @@sqlstatus = 0)
      begin
         fetch LoanCursor into @SourceID, @DelinqTyp, 
                       @SubPool, @OidSubPool, @ServicerID, @LoanNum, @ProspID
         select @Counter1 = @Counter1 + 1
      end
end

-- At this point the Cursor should be set to the 
-- record before the starting point  unless @sqlstatus = 2

if (@@sqlstatus = 2)
begin
    select 'The starting Point exceeds the Number of Records in the Set'
    close LoanCursor
    return
end

select @Counter2 = 0

while (@Counter2 < @PageSize and @@sqlstatus = 0)
   begin
      fetch LoanCursor into @SourceID, @DelinqTyp, @SubPool, 
                @OidSubPool, @ServicerID, @LoanNum, @ProspID

      insert into #Loan (SourceID, DelinqTyp, SubPool, OidSubPool, 
                                     ServicerID, LoanNum, ProspID)
      values (@SourceID, @DelinqTyp, @SubPool, @OidSubPool, 
	                       @ServicerID, @LoanNum, @ProspID)

     select @Counter2 = @Counter2 + 1
   end

close LoanCursor

So, as you can see, it is a lot of code, however, because regardless of the table, the code does the same thing, this type of stored procedure is highly generatable.

Method 4: Have the stored proc dump the results of your target query into a temp table with an identity field, then get the needed page based on the identity field.

Pros	The interface is simple as well. Network bandwidth and client memory are preserved for other things. Fairly simple in terms of SQL.
Cons	May take a large hit on the database if the resultset is large. Requires large tempdb.

Stored procedure like this is fairly simple to crank out, but you'll pay in the long term as the number of users increases. Eventually, your tempdb will have to be huge to accomodate all the temporary tables. Plus, eventually these tables will have to be torn down. Maintanance nightmare.

Conclusion
If at all possible, go with Method 1. It is simple, elegant, easy on the network, not too hard on the database. Your middle tier might suffer a bit if you have very large number of users, but so what, get another box.