Paginación Eficiente en SQL Server

Introducción

A veces los desarrolladores nos encontramos con el problema de tener que mostrar al usuario final una cantidad muy grande de registros procedentes de una base de datos. Esto conlleva no sólo un gran consumo de recursos del servidor de base de datos, de la red y de la aplicación cliente que habrá que intentar minimizar, sino también la frustración del usuario al ver lo lenta que es la aplicación. Este es un problema muy conocido que tiene también una solución muy conocida: la paginación. En vez de mostrar todos lo registros de una sóla vez, la aplicación muestra sólo una cantidad manejable de registros que llamamos página y permite al usuario navegar sobre el conjunto de páginas. En este artículo voy a discutir diferentes métodos para implementar una paginación eficiente en SQL Server 2000 y 2005.

Qué es una paginación eficiente

Una paginación eficiente es aquella que es capaz de presentar al usuario final una página en el menor tiempo posible y consumiendo la mínima cantidad posible de recursos. Estos recursos son:

• Ancho de banda de la red. Deberíamos consumir el mínimo ancho de banda posible, para ello, cada vez que se solicita una página, sólo debe viajar por la red la página solicitada. En algunas malas implementaciones de la paginación, cada vez que se solicita una página, circula por la red la totalidad de los registros, esto es inaceptable en la mayoría de los casos.
• Procesamiento del servidor. Deberíamos diseñar la paginación de manera que el servidor de base de datos pueda proporcionar la página solicitada con el mínimo consumo de procesador, memoria y acceso a disco. Para ello deberemos crear los índices adecuados y diseñar correctamente la manera de acceder a los datos.
• Procesamiento en el cliente. En la aplicación cliente deberíamos minimizar el consumo de procesador y memoria. Si la aplicación cliente consume demasiados recursos, tendremos aquí el cuello de botella.

Navegación básica o navegación completa

Está claro que la paginación tiene que permitir al usuario navegar por el conjunto de páginas. Lo que no está tan claro es exactamente qué funcionalidad debe proporcionar. Definamos por tanto dos tipos de navegación:

• Navegación básica. En la que se proporciona la siguiente funcionalidad:
  • Ir a la primera página
  • Ir a la última página
  • Ir a la página siguiente
  • Ir a la página anterior
• Navegación completa. En la que además se proporciona lo siguiente:
  • Número total de páginas
  • Ir a una página cualquiera

Veremos que se puede conseguir una implementación muy eficiente de la navegación básica. Sin embargo no se puede conseguir una implementación tan eficiente para a navegación completa, digamos que se puede conseguir una eficiencia razonablemente aceptable. Por tanto deberíamos intentar quedarnos con la navegación básica siempre que sea posible.

La implementación de ejemplo

En este artículo voy a implementar la paginación para la tabla Person.Contact de la base de datos de AdventureWorks que tiene alrededor de 20.000 registros, suficiente para tomarla como ejemplo. Sin embargo no es lo suficientemente grande como para apreciar a simple vista los problemas de la navegación completa.

Generalmente, al usuario final le mostraremos ciertos campos de la tabla, no todos, ordenados por alguno en concreto. Por ejemplo, supongamos que ordenamos por LastName y que mostramos los siguientes campos: ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone.

El índice de cobertura

El índice que más eficiente puede hacer una consulta es un índice de cobertura. Un índice de cobertura incluye todos los campos de la lista de selección de la consulta. Los campos que aparecen en la cláusula WHERE y en la cláusula ORDER BY forman la clave del índice. De esta manera SQL Server no tendrá que leerse toda la tabla y ordenarla sino que irá a buscarlos al índice que ya está ordenado y contiene todos los campos necesarios. Para crear este índice de cobertura, que hará mucho más eficiente la implementación de la paginación, ejecutamos la siguiente instrucción en SQL Server 2005:

CREATE INDEX Contact_LastName 
ON Person.Contact(LastName, ContactID) 
INCLUDE(FirstName, MiddleName, EmailAddress, Phone) 
      

La cláusula INCLUDE es nueva en SQL Server 2005. En SQL Server 2000 usaríamos la siguiente instrucción para crear el índice de cobertura:

CREATE INDEX Contact_LastName 
ON Person.Contact(LastName, ContactID, FirstName, 
                  MiddleName, EmailAddress, Phone)
        

Implementación de la navegación básica

Los siguientes procedimientos almacenados permiten obtener la primera y última página de contactos:

CREATE PROCEDURE GetContactsFirstPage 
    @PageSize int 
AS
    SELECT TOP (@PageSize) ContactID, FirstName, MiddleName, 
                LastName, EmailAddress, Phone 
    FROM Person.Contact
    ORDER BY LastName, ContactID 

GO

CREATE PROCEDURE GetContactsLastPage
    @PageSize int
AS
    SELECT TOP (@PageSize) ContactID, FirstName, MiddleName, 
                LastName, EmailAddress, Phone 
    FROM Person.Contact 
    ORDER BY LastName DESC, ContactID DESC

GO
        

Observa que para obtener sólo los registros de una página, se incluye la cláusula TOP. Además en la cláusula ORDER BY se incluye no sólo LastName, sino también la clave primaria ContactID, esto es necesario porque puede haber más de un contacto con el mismo LastName. En general, en cualquier paginación hay que incluir el la cláusula ORDER BY los campos por lo que se ordena más la clave primaria.

Cada vez que se obtiene una página de contactos de la base de datos, la aplicación cliente tiene que guardar el primer y último registro de la página para poder obtener luego la página anterior y la siguiente respectivamente.

Los siguientes procedimientos almacenados permiten obtener la página siguiente y la anterior:

CREATE PROCEDURE GetContactsNextPage 
    @PageSize int, 
    @BottomLastName nvarchar(50), 
    @BottomContactID int 
AS 
    SELECT TOP (@PageSize) ContactID, FirstName, 
                MiddleName, LastName, EmailAddress, Phone 
    FROM Person.Contact 
    WHERE LastName > @BottomLastName OR
          (LastName = @BottomLastName AND 
          ContactID > @BottomContactID) 
    ORDER BY LastName, ContactID
    
GO

CREATE PROCEDURE GetContactsPreviousPage 
    @PageSize int, 
    @TopLastName nvarchar(50), 
    @TopContactID int 
AS 
    SELECT TOP (@PageSize) ContactID, FirstName, 
                MiddleName, LastName, EmailAddress, Phone 
    FROM Person.Contact 
    WHERE LastName < @TopLastName OR 
          (LastName = @TopLastName AND 
          ContactID < @TopContactID) 
    ORDER BY LastName DESC, ContactID DESC 
GO 
        

@BottonLastName y @BottonContactID corresponden al último registro de la página actual y @TopLastName y @TopContactID corresponden al primer registro de la página actual.

Observa que las cláusulas WHERE resultan un poquito complicadas. No se compara sólo si es mayor o menor el LastName sino también el ContactID cuando los LastName son iguales. Si la ordenación fuera por dos campos, por ejemplo LastName y FirstName, la cláusula WHERE sería aún más compleja, para GetContactsNextPage sería:

....
WHERE LastName > @BottomLastName 
      OR
      (LastName = @BottomLastName AND 
       FirstName > @BottomFirstName) 
      OR
      (LastName = @BottomLastName AND 
      FirstName = @BottomFirstName AND 
      ContactID > @BottomContactID)
    

Esta técnica es tremendamente eficiente y puede implementarse de manera similar en otros sistemas de bases de datos. Cabe señalar que SQL Server 2000 no admite expresiones en la cláusula TOP, sólo literales. Así que en SQL Server 2000 habría que usar ejecución dinámina en los procedimientos almacenados, o bien, no usar procedimientos almacenados y generar la instrucción SQL en el cliente concatenando cadenas. Otra posibilidad sería fijar el tamaño de la página y eliminar el parámetro @PageSize. Si se decide usar ejecución dinámica, lo recomendable es usar sp_executesql. Para SQL Server 2000, el procedimiento GetContactsNextPage sería el siguiente:

CREATE PROCEDURE GetContactsNextPage 
    @PageSize int, 
    @BottomLastName nvarchar(50), 
    @BottomContactID int 
AS 
    DECLARE @sql nvarchar(500)
    SET @sql = N'
    SELECT TOP ' + CONVERT(nvarchar(10), @PageSize) + ' ContactID, FirstName, 
                MiddleName, LastName, EmailAddress, Phone 
    FROM Person.Contact 
    WHERE LastName > @BottomLastName OR
          (LastName = @BottomLastName AND 
          ContactID > @BottomContactID) 
    ORDER BY LastName, ContactID'

    EXEC sp_executesql @sql,
        N'@BottomLastName nvarchar(50), @BottomContactID int',
        @BottomLastName, @BottomContactID
    

Implementación de la navegación completa

Como he dicho anteriormente la navegación completa incluye, además de lo que proporciona la navegación básica, obtener una página cualquiera y el número total de páginas. Veremos que obtener una página cualquiera en SQL Server 2000 es bastante más complicado que en SQL Server 2005.

Obtener una página cualquiera en SQL Server 2005

Con la introducción de las funciones de ranking, ahora resulta bastante sencillo y razonablemente eficiente obtener una página cualquiera en SQL Server 2005. El siguiente procedimiento obtiene una página cualquiera:

CREATE PROCEDURE GetContactsPage 
    @PageSize int, 
    @PageNumber int 
AS 
    SELECT ContactID, FirstName, MiddleName, 
           LastName, EmailAddress, Phone 
    FROM ( 
            SELECT ContactID, FirstName, MiddleName, 
            LastName, EmailAddress, Phone, 
            ROW_NUMBER() OVER (ORDER BY LastName, ContactID) AS RowNumber 
            FROM Person.Contact 
    ) AS Contact 
    WHERE RowNumber BETWEEN @PageSize * @PageNumber + 1 
                    AND @PageSize * (@PageNumber + 1)
                     

Este método es muy eficiente para las primeras páginas, pero se va degradando linealmente el rendimiento según se va aumentando @PageNumber, ya que SQL Server tiene que leer todas las páginas anteriores antes de encontrar la página solicitada. Esto no suele ser un problema cuando el total de registros no supera unas cuantas decenas de miles, pero cuando se trata de millones puede resultar problemático. En caso de tratarse de millones de registros, lo mejor sería procurar reducir este número incluyendo filtros en la cláusula WHERE, ya que no tiene ningún sentido que el usuario final navegue por un número tan elevado de registros.

Obtener una página cualquiera en SQL Server 2000

Desafortunadamente en SQL Server 2000 no están disponibles las funciones de ranking. Así que habrá que utilizar otra técnica. Una forma relativamente sencilla, pero ineficiente, de obtener una página cualquiera, es la siguiente:

CREATE PROCEDURE GetContactsPage
    @PageSize int, 
    @PageNumber int 
AS
    DECLARE @sql nvarchar(500)
    SET @sql = N'
    SELECT TOP ' +  CONVERT(nvarchar(10),@PageSize) + N'ContactID, FirstName, MiddleName, 
                   LastName, EmailAddress, Phone 
    FROM Person.Contact
    WHERE ContactID NOT IN (
            SELECT TOP ' + CONVERT(nvarchar(10),@PageSize * @PageNumber) + N' ContactID
            FROM Person.Contact
            ORDER BY LastName, ContactID
    )
    ORDER BY LastName, ContactID'

    EXEC sp_executesql @sql
    

Este última técnica funciona bien para las primeras páginas, pero el rendimiento se degrada muy rápidamente al aumentar @PageNumber.  Para un @PageNumber = 100 y @PageSize = 100 este método tardó 16 segundos en ejecutarse en mi PC de escritorio, mientras que el método anterior (el de ROW_NUMBER()), tardó sólo unos cuantos milisegundos. Como puedes ver, este es un buen ejemplo de lo que no se debe hacer. Curiosamente este ejemplo de la MSDN usa esta técnica. Es una lástima, porque es un excelente ejemplo de optimización de la aplicación cliente, pero es funesto en cuanto al servidor de base de datos se refiere.

En SQL Server 2000 la manera más eficiente de obtener una página cualquiera es usar cursores de servidor. Los cursores de servidor tienen muy mala fama y con razón, pero en este caso es lo más adecuado.

En SQL Server hay dos tipos de cursores: cursores T-SQL y cursores del API. El siguiente procedimiento almacenado obtiene una página cualquiera usando un cursor de servidor T-SQL:

CREATE PROCEDURE GetContactsPage 
    @PageSize int, 
    @PageNumber int 
AS 
    DECLARE cContact CURSOR DYNAMIC READ_ONLY FOR 
        SELECT ContactID, FirstName, MiddleName, 
               LastName, EmailAddress, Phone 
        FROM Person.Contact 
        ORDER BY LastName, ContactID 
    OPEN cContact 
    DECLARE @n int 
    DECLARE @FirstRecord int 
    SET @FirstRecord = @PageSize * @PageNumber + 1 
    FETCH RELATIVE @FirstRecord FROM cContact 
    SET @n = 1 
    WHILE @n < @PageSize AND @@FETCH_STATUS = 0
    BEGIN 
        FETCH cContact 
        SET @n = @n + 1 
    END 
    CLOSE cContact 
    DEALLOCATE cContact
    

El problema que tiene este último procedimiento es que la aplicación cliente recibe un conjunto de registros por cada registro que haya en la página, lo cual resulta engorroso y añade una sobrecarga innecesaria. Sin embargo, este procedimiento, aunque no es tan eficiente como el de ROW_NUMBER(), es aceptablemente eficiente. Podría evitarse devolver un conjunto de registros por cada registro de la página guardando primero los registros devueltos por el cursor en una tabla temporal y al final devolviendo todos los registros de la tabla temporal de golpe, así se reduciría el tráfico de red, pero aumentaría la carga del servidor y terminaríamos con un procedimiento almacenado bastante más complicado y difícil de mantener.

Los cursores de servidor de API están diseñados para usarse desde el API de acceso a datos, incluyendo ODBC, OLEDB y ADO. Lamentablemente en ADO.NET no se han llegado a implementar, se incluyeron en la Beta 1 de .NET Framewok 2.0, pero desaparecieron ya en la Beta 2. Así que si queremos usarlos tendríamos que recurrir a ADO clásico. El siguiente método de VB.NET carga una página de contactos en un DataTable usando ADO clásico y un cursor de servidor del API dinámico de sólo lectura:

Sub FillContactsPage(ByVal ContactsTable As DataTable, ByVal PageSize As Integer, ByVal PageNumber As Integer)
    Dim cn As New ADODB.Connection
    cn.ConnectionString = _
	"Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=(local);Initial Catalog=AdventureWorks"
    Dim rst As New ADODB.Recordset
    cn.Open()
    Dim query As String = "SELECT " & _
                        " ContactID, FirstName, MiddleName, " & _
                        " LastName, EmailAddress, Phone " & _
                        " FROM     Person.Contact " & _
                        " ORDER BY LastName, ContactID"
    rst.CursorLocation = ADODB.CursorLocationEnum.adUseServer
    rst.Open(query, cn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly)
    rst.Move(PageSize * PageNumber)
    rst.CacheSize = PageSize
    For i = 1 To PageSize
        If rst.EOF Then Exit For
        Dim row As DataRow = ContactsTable.NewRow()
        row("ContactID") = rst("ContactID").Value
        row("FirstName") = rst("FirstName").Value
        row("MiddleName") = rst("MiddleName").Value
        row("LastName") = rst("LastName").Value
        row("EmailAddress") = rst("EmailAddress").Value
        row("Phone") = rst("Phone").Value
        ContactsTable.Rows.Add(row)
        row.AcceptChanges()
        rst.MoveNext()
    Next
    rst.Close()
    cn.Close()
End Sub

Este método es mejor que el anterior en que recibe todos los registros de la página en un sólo conjunto de registros, pero es peor en que no se puede encapsular en un procedimiento almacenado y en que produce más llamadas al servidor.

Los cursores de servidor del API están implementados a base de llamadas a procedimientos almacenados no documentados. Que no esté documentados significa que no obtendremos soporte de Microsoft sobre problemas que puedan causar su uso, pero eso no quiere decir que no podamos usarlos, además puede encontrarse documentación aquí.

El siguiente procedimiento almacenado usa los procedimientos almacenados de cursores del API para obtener una página cualquiera. Utiliza un cursor hacia delante de sólo lectura (forward only - read only):

CREATE PROCEDURE GetContactsPage 
    @PageSize int, 
    @PageNumber int 
AS 
    DECLARE @Handle int 
    DECLARE @TipoCursor  int 
    SET @TipoCursor=4 -- Forward only
    DECLARE @TipoBloqueo int 
    SET @TipoBloqueo =1 -- Read only
    DECLARE @Consulta nvarchar(4000) 
    SET @Consulta = N'SELECT ContactID, LastName, FirstName, EmailAddress, Phone 
                      FROM Person.Contact ORDER BY LastName, ContactID' 
    DECLARE @rowcount  int -- será = -1 siempre
    EXEC sp_cursoropen @Handle OUTPUT, @Consulta, @TipoCursor OUTPUT, @TipoBloqueo OUTPUT, @rowcount OUTPUT

    DECLARE @TipoDesplazamiento int 
    SET @TipoDesplazamiento = 32 -- Relative 
    DECLARE @FirstRecord int 
    SET @FirstRecord = @PageSize * @PageNumber + 1 
    EXEC sp_cursorfetch @Handle, @TipoDesplazamiento, @FirstRecord , @PageSize 

    EXEC sp_cursorclose @Handle 
    

Este procedimiento devuelve dos conjuntos de registros. El primero lo devuelve sp_cursoropen y está vacío, solo contiene información de metadatos. El segundo conjunto de registros lo devuelve sp_cursorfetch y contiene todos los registros de la página. Esta técnica es relativamente simple y la más eficiente para obtener una página cualquiera de contactos en SQL Server 2000. La pega es que usa procedimientos almacenados no documentados.

Obtener el número de registros

De poco serviría poder ir a una página cualquiera si no sabemos cuantas páginas hay, y para ello hay que saber cuantos registros devuelve la consulta. Obtener el número de registros que devuelve una consulta es muy sencillo utilizando COUNT(*). Para nuestro caso de ejemplo sería:

CREATE PROCEDURE GetContactsRows
    @Rows int OUTPUT
AS
    SELECT @Rows = COUNT(*)
    FROM Person.Contact

La pega de este procedimiento almacenado es que SQL Server tiene que leer todos los registros para contarlos. En realidad SQL Server leerá todos los registros del índice más pequeño que tenga la tabla. Si la consulta devuelve una gran cantidad de registros, esto puede tardar más de lo deseable y sobrecargar el servidor.

Una alternativa a COUNT(*) es obtener una cuenta de registros aproximada. Antes de explicar como obtener esta cuenta de registros aproximada, quisiera discutir su utilidad. Imaginemos que estamos en una página web que implementa la paginación de los contactos, el sistema averigua el número de páginas por medio de COUNT(*) y nos muestra un enlace por cada página. Nos entretenemos un rato navegando por las páginas de contactos y por fin nos da por pulsar el enlace correspondiente a la última página. Podría ocurrir que en el momento de solicitar esa página, hubiera más páginas que se hayan insertado después de que empezáramos con la web, también podría ocurrir que haya menos registros porque se han eliminado contactos y lo que creíamos que era la última página ahora ya no existe tal página. En definitiva, aunque COUNT(*) nos da el número de registros que hay en un momento dado, ese número de registros puede variar al cabo de un rato, por tanto, al cabo de un rato sólo es un valor aproximado del número de registros que devuelve la consulta. ¿Entonces sería útil un valor aproximado calculado desde el principio? Yo diría que casi tan útil como el COUNT(*). Ahora la cuestión es ¿podemos vivir con valores aproximados del número de registros?. La respuesta podría ser otra pregunta ¿Por qué no?. La cuestión es que nuestra aplicación funcione de forma razonable. Cuando el usuario se posiciona en lo que al principio era la última página, comprobamos si la página está vacía, en cuyo caso concluimos que se han borrado registros y por tanto la última página debe ser alguna de las anteriores, así que hacemos desaparecer el enlace. Si vemos que la página está completa, concluimos que habrá más páginas, así que añadimos un enlace para la página siguiente.

Veamos ahora como obtener el valor aproximado. Ese número de registros aproximado que devuelve una consulta lo podemos obtener por medio de su plan de ejecución. Si ejecutamos lo siguiente en el analizador de consultas o en el Management Studio:

SET SHOWPLAN_ALL ON 
GO 
SELECT ContactID, LastName, FirstName, EmailAddress, Phone 
FROM Person.Contact ORDER BY LastName, ContactID
GO 
SET SHOWPLAN_ALL OFF
GO 

En vez de ejecutarse la consulta devuelve un conjunto de registros que describe el plan de ejecución, que contiene, entre otra información, el número de registros estimado que devolvería la consulta al ejecutarse. SQL Server calcula este número basándose en información estadística que tiene almacenada y es mucho más eficiente que usar COUNT(*).

Manteniendo el estilo que he seguido hasta ahora de encapsular todo el acceso a datos en procedimientos almacenados, falta pues escribir un procedimiento almacenado que devolviera el número de registros aproximado en la tabla de contactos. La pega es que no es posible escribir un procedimiento almacenado T-SQL que lo haga, la razón es que SET SHOWPLAN_ALL ON tiene que ser la única instrucción del proceso por lotes y en un procedimiento almacenado T-SQL sólo hay un proceso por lotes. Entonces o bien lo hacemos desde la aplicación cliente o bien escribimos un procedimiento almacenado CLR en VB.NET o C#.

Esta es la versión VB.NET del procedimiento almacenado:

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub GetContactsEstimateRows( <Out()> ByRef EstimateRows As SqlInt64)
        Dim cn As New SqlConnection("context connection=true")
        Dim cmdEnablePlan As New SqlCommand("SET SHOWPLAN_ALL ON", cn)
        Dim cmdDisablePlan As New SqlCommand("SET SHOWPLAN_ALL OFF", cn)
        Dim cmdSelect As New SqlCommand("SELECT * FROM Person.Contact", cn)
        cn.Open()
        cmdEnablePlan.ExecuteNonQuery()
        Dim reader As SqlDataReader = cmdSelect.ExecuteReader()
        EstimateRows = SqlInt64.Null
        If reader.Read() Then
            EstimateRows = New SqlInt64(Convert.ToInt64(reader("EstimateRows")))
        End If
        reader.Close()
        cmdDisablePlan.ExecuteNonQuery()
        cn.Close()
    End Sub
End Class

Una vez compilado, registrado el ensamblado en SQL Server y creado el procedimiento almacenado. Podríamos ejecutarlo desde el Management Studio:

DECLARE @EstimateRows bigint 
EXEC GetContactsEstimateRows @EstimateRows OUTPUT 
SELECT @EstimateRows AS EstimateRows 

Y nos muestra 19972. Casualmente el número exacto. Lo mismo que devuelve SELECT COUNT(*) FROM Contacts

Conclusiones

• Un índice apropiado puede acelerar espectacularmente una consulta y la paginación no es una excepción.
• Lo más eficiente para la paginación es una navegación básica.
• Para ir a una página cualquiera lo más sencillo y eficiente es usar ROW_COUNT() en SQL Server 2005. En SQL Server 2000, los cursores de servidor.
• Un valor estimado de cuenta de filas obtenido a partir del plan de ejecución es más eficiente que la cuenta exacta y puede ser igual de útil.

 

---

Espacios de nombres usados en el código de este artículo:

System.Data
System.SqlClient
Microsoft.SqlServer
ADODB