Wednesday, June 6, 2007

Optimized paging in queries over two or more tables using Sql Server 2005 functions


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)

5 comments:

Gustavo Bonansea said...

A ver si en este escribís más seguido :D

Un abrazo.

Tincho said...

Buenísimo !!!, cambiaste el R12 por un Megane.
Saludos.

Martín Olivares said...

Haré un intento, lo que pasa es que tengo que buscar algo "original".

Un abrazo!

Tincho said...

Buenisimo, por fin cambiaste el R12 por el Megane. Suerte.

Martín Olivares said...

Jajaja, mas que un Megane es un Honda Civic. La migración de Spaces a Blogger es una evolución la verdad.