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.
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.
