GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

Paginar datos en SQL Server: ROW_NUMBER, TOP y otras alternativas eficientes


Una problemática típica en el desarrollo de aplicaciones de bases de datos es la Paginación de Conjuntos de Registros, es decir, ejecutar una consulta mostrando los datos en páginas (ej: de 10 en 10). Quizás la forma más fácil sea llenar (Fill) un DataSet con todas las filas de la consulta, y en la presentación al usuario mostrarlas (maquillar) de forma paginada al utilizar las propiedades de paginación de los controles de datos como el DataGrid. El inconveniente, ejecutar la consulta completa y transferir todas las filas desde SQL Server a la aplicación. Pero ¿Cómo paginar eficientemente en SQL Server?

Descripción del Problema

Ejecutar una consulta SQL y paginar utilizando las propiedades de paginación de controles de datos como el DataGrid, puede ser una aproximación suficiente para consultas SQL ligeras que manejan pequeños conjuntos de registros (es lo que se llama, consultas de chichi-nabo, como decía una antigua jefa mía ;-).

Uno de los principales inconvenientes de esta forma de trabajar (y estoy generalizando), especialmente en el desarrollo de Aplicaciones Web, es que cada vez que el usuario desea mostrar una página diferente del conjunto de datos, se volverá a ejecutar de nuevo TODA la consulta SQL para hacer de nuevo el Bind de datos, y además, para tan sólo mostrar unos pocos registros. Bueno, depende de cómo esté programado, evidentemente se podrían utilizar técnicas de cacheo para cachear el DataSet en memoria y evitar no ejecutar la PEDAZO-CONSULTA no-se-cuantas-veces, pero claro, entonces, podremos incurrir en problemas de memoria.

Cuando trabajamos con consultas SQL más complejas (por ejemplo, al trabajar con Tablas Versionadas) y conjuntos de registros más voluminosos, necesitaremos prestar cierta atención adicional para evitar sufrir problemas de Rendimiento en SQL Server, sean del tipo que sean.

Uno de los principales trucos para mejorar el rendimiento de las consultas SQL de nuestras aplicaciones es paginar directamente en SQL Server, es decir, si nuestra consulta SQL maneja 20.000 registros, evitar ejecutar la consulta SQL completa transfiriendo los 20.000 registros desde SQL Server a la aplicación para que esta pagine de 10 en 10, y en su lugar, ejecutar una consulta SQL equivalente que tan sólo nos devuelva los 10 registros correspondientes a la página que se desea mostrar. Es aquí, donde en muchas ocasiones podremos mejorar notablemente el rendimiento de nuestras aplicaciones. ¿Tecnología o Picaresca? Eh ahí la cuestión.

A continuación, vamos a comentar dos alternativas para paginar eficientemente en SQL Server utilizando Transact-SQL. En función del modelo de datos que estemos utilizando y los detalles propios de nuestra consulta SQL, puede interesarnos más aprovechar una u otra de estas dos aproximaciones (o quizás alguna otra alternativa aquí no descrita, o incluso una mezcla de todas ellas).  Empezamos.

Paginar en SQL Server con ROW_NUMBER, ORDER BY, y WHERE: Acceso Aleatorio y Secuencial

Quizás la alternativa para paginar en SQL Server de una forma más genérica, es decir, que pueda servir para más casos, sea la utilización de la función de Ranking ROW_NUMBER, disponible a partir de SQL Server 2005.

Gracias a ROW_NUMBER, podemos crear una Tabla Derivada o una CTE (Common Table Expression) basada en la consulta que deseamos paginar, de tal modo, que cada fila del conjunto de resultados tendrá un valor numérico secuencial asociado, conforme al orden establecido.

A todo esto, recordar que al utilizar ROW_NUMBER es obligatorio utilizar la cláusula OVER con ORDER BY, es decir, estamos obligados a establecer un orden a nuestra consulta. Esto tiene cierto sentido, ya que la única forma de poder garantizar mínimamente que dos ejecuciones de una misma consulta van a devolver el mismo conjunto de resultados (es decir, con el mimos valor de ROW_NUMBER asociado a cada fila) es forzando un criterio de orden. Sin esto, sería imposible que pudiéramos paginar, ya que cada vez que el usuario desee mostrar una página, deberemos volver a ejecutar la consulta, luego la asignación correcta del ROW_NUMBER es crítico para un correcto funcionamiento. En consecuencia, la elección de los campos del ORDER BY y del tipo de orden, es a su vez, una elección igualmente importante.

Pues bien, una vez que tenemos escrita nuestra consulta SQL utilizando ROW_NUMBER desde una Tabla Derivada (Derived Table) o desde una CTE (Common Table Expression), tan sólo nos queda filtrar el conjunto de resultados, por ejemplo con un BETWEEN sobre el ROW_NUMBER.

Sin embargo, el hecho de ejecutar una consulta SQL que tan sólo nos devuelva n filas (ej: 10 filas, si estamos paginando de 10 en 10), implica que sólo con esta información, no podremos saber si estamos visualizando la última página (por ejemplo, para poder deshabilitar la navegación a la página siguiente), ni podremos conocer el número de páginas disponibles. Una forma para solucionar este problema, es ejecutar un SELECT COUNT que nos indique el número de registros resultantes correspondientes a nuestra consulta SQL, para de este modo, poder calcular el número de páginas disponibles, así como poder conocer si estamos o no en la última página.

Básicamente, esta es la idea de la paginación con ROW_NUMBER en SQL Server. Podemos seguir profundizando en esta idea y analizar muchos detalles de su comportamiento. Por ejemplo, ¿cómo se comportará este método de paginación, si mientras un usuario está navegando de página a página se inserta una nueva fila?

Este método de paginación tiene varias ventajas, principalmente, que puede aplicarse a cualquier consulta SQL, y además, que permite acceder directamente a cualquier página de datos (es decir, permite acceso aleatorio y acceso secuencial).

Paginar en SQL Server con TOP, ORDER BY, y WHERE: Acceso Secuencial

Una de las principales alternativas de paginación es utilizar un SELECT TOP en nuestra consulta SQL, por ejemplo, un SELECT TOP 10 si deseamos paginar de 10 en 10. El truco, por un lado utilizar un ORDER BY en la consulta SQL, por el mismo motivo que se comentaba en el caso anterior (al paginar con ROW_NUMBER). pero la cosa no queda aquí, ya que con esto podemos obtener la primera página, pero, ¿cómo poder acceder a la siguiente página?

Para poder utilizar este método de paginación, es requisito que los datos resultantes de nuestra consulta SQL sean ordenados por un campo que sea único. Bueno, también podrían ser varios campos (ej: una clave compuesta). Vamos a poner como ejemplo un Blog, en el que tenemos una tabla que almacena las Páginas Web (TBL_PAGINAS), la cual contiene un campo que almacena la Fecha y Hora de Publicación de cada página (FEC_PUBLICACION), el cuál es único (no permite valores duplicados). OK. En este caso, una vez que he mostrado la primera página, tengo los datos de las 10 primeras filas, ¿Verdad?. Bien, pues el truco es que almacenemos el valor del campo FEC_PUBLICACION de la última fila (ej: en una variable, utilizarlo en una QueryString, etc.). De este modo, para acceder a la siguiente página, tan sólo tendríamos que ejecutar la consulta SQL con un SELECT TOP 10, nuestro ORDER BY (requisito), y una WHERE para especificar que deseamos FEC_PUBLICACION sea mayor que el valor de la última fila de la página que estamos mostrando actualmente.

Del mismo modo, cuando estamos mostrando una página que no sea la primera, nos interesará poder consultar la página anterior, para lo cual utilizaremos el mismo razonamiento, aunque en esta ocasión nos interesará quedarnos con el valor del campo FEC_PUBLICACION de la primera fila de la página que estamos visualizando. En consecuencia, es deseable conocer los valores MAX y MIN, para así saber cuando estamos en la primera o en la última página, y poder proporcionar al usuario una experiencia de navegación congruente.

Este método de Paginación nos permite tan sólo un acceso secuencial a nuestras páginas de datos, es decir, NO podremos realizar un acceso aleatorio, ya que para acceder a la página N necesitaríamos tener el valor del campo FEC_PUBLICACION del último registro de la página N-1. Esta limitación (el acceso secuencial), junto al requisito de necesitar un campo único como criterio de ordenación, pueden producir que tengamos que descartar este método de paginación en más de una ocasión.

Con esto queda descrita la idea de la paginación con TOP, ORDER BY y WHERE en SQL Server, aunque igual que comentábamos en el caso anterior, aún es posible profundizar en otros muchos detalles, como por ejemplo ¿cómo se comportará este método de paginación, si mientras un usuario está navegando de página a página se inserta una nueva fila?

Código de ejemplo

No mas charla. La teoría está muy bien, pero al final, hace falta tener algún ejemplo, que sirva para jugar, aclarar dudas y conceptos, etc. A continuación, se puede descargar un ZIP con un código SQL de ejemplo, el cual, crea un tabla con datos de ejemplo, crea varios procedimientos almacenados, y por último muestra varias ejecuciones de ejemplo de dichos procedimientos almacenados. En reducidas cuentas:

  • Hay un procedimiento almacenado (dbo.GetPaginasRowNumber) para paginar con ROW_NUMBER, al cual, le acompaña otro procedimiento almacenado que devuelve el Count (dbo.GetCountPaginas), para saber cuantas filas se están paginando.
  • Hay dos procedimientos almacenados para paginar con TOP, utilizando acceso aleatorio, de tal modo, que un procedimiento almacenado se utilice para paginar hacia adelante (dbo.GetPaginasTOPmayorQue) y el otro para paginar hacia atrás (dbo.GetPaginasTOPmenorQue).

Espero que este pequeño código de ejemplo puede resultar útil para entender todo esto.

Descargar código SQL de ejemplo para Paginar en SQL Server (Paginar_SQLServer_ROW_NUMBER_TOP.zip)

Conclusiones y Despedida

Es importante evaluar cuál es la aproximación que más nos interesa. El modelo de datos que utilicemos y las necesidades propias de nuestra consulta SQL y de nuestra aplicación, serán muy probablemente los factores más determinantes. No hay reglas. Quizás nos interese una mezcla de ambas aproximaciones, quizás tengamos que utilizar alguna otra alternativa, en fin, cada caso habrá que verlo.

Puede resultarnos necesario utilizar Trazas de SQL, para probar diferentes alternativas y medir de forma objetiva el coste de la ejecución de cada una de las alternativas. Téngase en cuenta, que habitualmente no cuesta lo mismo consultar la primera página que la última página, por lo tanto, si deseamos realizar un análisis de coste con un mínimo de profundidad, necesitaremos tener en cuenta este detalle. Del mismo modo, quizás los usuarios, muy habitualmente tan sólo naveguen por las primeras páginas, en cuyo caso el mayor coste de las últimas páginas pueda pasarse por alto. Lo dicho. No hay reglas.

Por último quería recomendar la lectura de un Post de Itzik Ben-Gan (uno de los grandes) en el que habla también del paginamiento de datos en SQL Server. Se trata del Post Paging in SQL Server 2005. Aquí podemos ver varias cosas interesantes, como el comportamiento peculiar de IDENTITY en SQL Server 2000 (que a estas altura tiene poca importancia, pero aún así, es un detalle curioso) y también la utilización de TOP, CROSS APPLY, y tablas temporales para el paginamiento de datos.

Poco más por hoy. Como siempre, confío que la lectura resulte de interés.

 


[Fecha del Artículo (UTC): 31/12/2011]
[Autor: GuilleSQL]



Escribir un Comentario

Para poder escribir un comentario, debe Iniciar Sesión con un usuario.

Si no dispone de un usuario, puede Registrarse y hacerse miembro.

Si dispone de un usuario, pero no recuerda sus credenciales de acceso, puede Restablecer su Contraseña.

Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Enero de 2017 (4)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Esta información se proporciona "como está" sin garantías de ninguna clase, y no otorga ningún derecho.
This information is provided "AS IS" with no warranties, and confers no rights.

Copyright © 2007 GuilleSQL, todos los derechos reservados.
GuilleSQL.com y GuilleSQL.net son también parte de Portal GuilleSQL.

Visitas recibidas (Page Loads) en GuilleSQL (fuente: StatCounter):

screen resolution stats
Visitas