The default paging made by the ASP.Net controls that support pagination is based on the following steps:
1) Receive the DataSource with the result of the query
2) Based on the page size and the page selected by the user, it selects the records of the "selected page" to show, discarding the rest of the data. This pagination is highly expensive in terms of resources (greater traffic and data processing), when the volume of records on which the query is made is of a considerable size.
For example, a user has 200 Orders in the database and a form with gridview that shows 10 records for each page. If he browse 10 grid pages, the amount of records transferred to gridview so that this pagine will be of 2000 (200 records by each selected page) whereas the shown amount indeed is of 100 records. That is that 1900 records are transferred only to be discarded, by all means that this waste exponentially increases with the amount of records that the query gives back. The example of custom paging which we can easily find in the Web, makes use of function ROW_NUMBER() of SQL Server 2005, and only is useful when the query is made on an single table. At the time of making queries on related tables function ROW_NUMBER() does not apply, since to a registry “father” (as the head of an order) would assign so many RowNumbers as “children” (order details) records have. Therefore, looking for the new functions of SQL Server 2005 to solve this problem, I found the DENSE_RANK() function that allows to obtain only one RowNumber by each registry “father” in a query over multiple tables. Thus, I developed an stored procedure for the AdventureWorks database that gives back a certain page of result of a query over Orders and its detail in optimized form, this can be useful when need populate paged hierarchical data like Orders & Order Details and want make to do a single call to retrieve all data of a single page.
Stored procedure PagedOrders
CREATE PROCEDURE [dbo].[PagedOrders]
@pagesize int,
@pageindex int
AS
SET NOCOUNT ON;
SELECT * FROM
(
SELECT DENSE_RANK() OVER (ORDER BY Sales.SalesOrderHeader.SalesOrderID DESC) AS Row,
Sales.SalesOrderHeader.SalesOrderID,
Sales.SalesOrderHeader.OrderDate,
Sales.SalesOrderHeader.DueDate,
Sales.SalesOrderHeader.PurchaseOrderNumber,
Sales.SalesOrderHeader.SubTotal,
Sales.SalesOrderDetail.OrderQty,
Sales.SalesOrderDetail.UnitPrice,
Sales.SalesOrderDetail.ProductID,
Sales.SalesOrderDetail.UnitPriceDiscount,
Sales.SalesOrderDetail.LineTotal,
Production.Product.Name
FROM Sales.SalesOrderDetail
INNER JOIN
Sales.SalesOrderHeader
ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN
Production.Product
ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
)
AS Result
WHERE Row between ((@pageindex - 1) * @pagesize + 1) and (@pageindex * @pagesize)
