Recipe 9.4: Improving Paging Performance

Problem

Given an application that allows theuser to page through
a large result set in a data grid, you need to improve the performance of the
paging.

Solution

Build a custom paging solution that overcomes the performance limitations of
the overloaded Fill( ) method of the DataAdapter.

The sample uses a single stored procedure, which is shown in Example
9-5:

SP0904_PageOrders

Used to return 10 records from the Orders table of the Northwind database
that correspond the first, last, next, or previous page, or a specific page.
The procedure has the following arguments:

@PageCommand

An input parameter that accepts one of the following values: FIRST, LAST, PREVIOUS, NEXT, or GOTO. This specifies the page of results to return to the
client.

@FirstOrderId

An input parameter that contains the OrderID of
the first record of the client's current page of Orders data.

@LastOrderId

An input parameter that contains the OrderID of
the last record of the client's current page of Orders data.

@PageCount

An output parameter that returns the number of pages, each of which
contains 10 records, in the result set.

@CurrentPage

An output parameter that returns the page number of the result set
returned.

Example 9-5. Stored procedure: SP0904_PageOrders

ALTER PROCEDURE SP0904_PageOrders
@PageCommand nvarchar(10),
@FirstOrderId int = null,
@LastOrderId int = null,
@PageCount int output,
@CurrentPage int output
AS
SET NOCOUNT ON
select @PageCount = CEILING(COUNT(*)/10) from Orders
-- first page is requested or previous page when the current
-- page is already the first
if @PageCommand = 'FIRST' or (@PageCommand = 'PREVIOUS' and
@CurrentPage <= 1)
begin
select top 10 *
from Orders
order by OrderID
set @CurrentPage = 1
return 0
end
-- last page is requested or next page when the current
-- page is already the last
if @PageCommand = 'LAST' or (@PageCommand = 'NEXT' and
@CurrentPage >= @PageCount)
begin
select a.*
from
(select TOP 10 *
from orders
order by OrderID desc) a
order by OrderID
set @CurrentPage = @PageCount
return 0
end
if @PageCommand = 'NEXT'
begin
select TOP 10 *
from Orders
where OrderID > @LastOrderId
order by OrderID
set @CurrentPage = @CurrentPage+1
return 0
end
if @PageCommand = 'PREVIOUS'
begin
select a.*
from (
select TOP 10 *
from Orders
where OrderId < @FirstOrderId
order by OrderID desc) a
order by OrderID
set @CurrentPage = @CurrentPage-1
return 0
end
if @PageCommand = 'GOTO'
begin
if @CurrentPage < 1
set @CurrentPage = 1
else if @CurrentPage > @PageCount
set @CurrentPage = @PageCount
declare @RowCount int
set @RowCount = (@CurrentPage * 10)
exec ('select * from
(select top 10 a.* from
(select top ' + @RowCount + ' * from Orders order by OrderID) a
order by OrderID desc) b
order by OrderID')
return 0
end
return 1

The sample code contains six event handlers and a single method:

Form.Load

Sets up the sample by loading the schema for the Orders table from the
Northwind database into a DataTable. Next, a DataAdapter is created to select records using the stored
procedure to perform the paging through the DataTable.
The GetData( ) method is called to load the first page
of Orders data.

GetData( )

This method accepts a page navigation argument. The parameters for the
stored procedure created in the Form.Load event are
set. The Fill( ) method of the DataAdapter is called to execute the stored procedure to
retrieve the specified page of records and the output parameters of the stored
procedure—@PageCount and @CurrentPage—are retrieved.

Previous Button.Click

Calls the GetData( ) method with the argument PREVIOUS to retrieve the previous page of data from the
Orders table.

Next Button.Click

Calls the GetData( ) method with the argument NEXT to retrieve the next page of data from the Orders
table.

First Button.Click

Calls the GetData( ) method with the argument FIRST to retrieve the first page of data from the Orders
table.

Last Button.Click

Calls the GetData( ) method with the argument LAST to retrieve the last page of data from the Orders
table.

Goto Button.Click

Sets the value of the current page to the specified page value and calls
the GetData( ) method with the argument GOTO to retrieve that page of data from the Orders
table.

Discussion

Overloads of the Fill( ) method of the DataAdapter allow a subset of data to be returned from a
query by specifying the starting record and the number of records to return as
arguments. This method should be avoided for paging through result
sets-especially large ones-because it retrieves the entire result set for the
query and subsequently discards the records outside of the specified range.
Resources are used to process the entire result set instead of just the subset
of required records.

The solution shows how to create a stored procedure that will return a result
set corresponding to a page of data from a larger result set. The TOP and WHERE clauses are used
together with the primary key (any unique identifier would do) and the sort
order. This allows first, last, next, and previous paging. The goto paging is
done by nesting SELECTTOPn statements with alternate ascending and descending
sorts to get the subset of the records for the page specified. The gotoselect statement uses a dynamic
SQL statement executed using the T-SQL EXEC command. This
allows a variable number of TOPn records to be selected within the statement. The EXEC command could also be used to dynamically calculate the
top records for all statements so that the number of records per page could be
supplied asan input parameter to the stored procedure rather
than hardcoded.