Paginación de registros, V2.0

Hace algún tiempo discutimos con algunos amigos sobre los métodos de paginación que utilizamos en SQL Server. En esa discusión Luis y yo expusimos nuestros métodos con ventajas y desventajas para ambos.


Hoy no voy a entrar en detalle a explicar el método de Luis, sino que mostraré con un ejemplo como llevar un procedimiento almacenado para una consulta larga, a un sistema paginado eficientemente.


Antes de comenzar debo aclarar algunas cosas para evitar comentarios a posteriori. Este sistema de paginación utiliza dos procedimientos almacenados y caché. En ciertos casos es necesario hacer dos bajadas a la base de datos, en otros uno. A pesar de que esto pueda sonar contraproducente con respecto a la eficiencia, la forma en que consulta y trae los datos es muy interesante, y es un costo que se puede pagar por lograr estos objetivos. También debo aclarar que el desarrollo es mayor que con otros sistemas, pero insisto en que los resultados demuestran que vale la pena.


Y por último, debo aclarar también que este sistema no lo inventé yo, sino que lo tomé prestado de mi amigo Carlos Mercado. No se si el lo inventó o lo tomó prestado de otro lugar, y eso tampoco es importante ahora.


Veamos entonces como funciona. Los modelos y los procedimientos pertenecen a la empresa donde yo trabajo, y por lo tanto representan algo real y no una demo.


Supongamos un sistema de órdenes de compra (almacenadas en una tabla OC), que posee referencia a un centro de gestión (ORGC), como también a los usuarios de la empresa creadora de la orden de compra (USUARIOS). Por último, una tabla con los diferentes estados en los que la orden de compra puede estar (ESTADOSDOC). En los procedimientos almacenados encontrarán otra tabla moneda, la que almacena información de la moneda con que se hizo la orden de compra.



Diagrama con las tablas involucradas en las consulta


Un procedimiento almacenado que lista las órdenes de compra de cierto centro de gestión, para ciertos usuarios con determinados roles y haciendo los joins entre las tablas mencionadas, posee código tsql como el que pueden ver en el siguiente link.


A simple vista puede verse que hay mucho código y eso puede confundir, pero prefiero hacer un ejemplo con algo real y ver como se implementa con algo mas que un select * from tabla.


En la discusión anterior, yo planteaba que hay que separar éste procedimiento almacenado en dos nuevos procedimientos. La gran mayoría de las consultas de sql poseen la siguiente estructura:


Select
            campos
From
            tablas
Where
            Condiciones.


Lo que mi sistema plantea (ya aclaré que no es de mi autoría) es dividir esta consulta en dos subconsultas, una donde predomina el where (consulta 1) y otra donde predomina el select (consulta 2). Debo mencionar antes de continuar que es necesario que la tabla principal sobre la que se hace la consulta, en este caso OC, posea una columna única en datos, idealmente un identity. Se podría hacer con una llave de más de un campo, pero eso complicaría mucho la segunda consulta.


¿Por que esta separación de consultas? Con la primera consulta se filtran los resultados basándose en los criterios de búsqueda, es decir, todo o casi todo lo que va en el where. Por lo general es casi todo.


¿Qué deseo obtener de esta consulta 1? El resultado de la consulta 1 es una lista de identificadores únicos de registros de la tabla principal, que se utilizará para la consulta 2.


¿Dónde esta la eficiencia en esta consulta 1? Si voy a traer resultados sobre una cantidad muy grande de datos, traigo información mínima de ellos (lista de identificadores) y la parte “pesada” de la consulta ya la realicé con el where.


¿Cómo quedaría el procedimiento almacenado que estamos estudiando, para el caso 1? Pueden ver el código aquí. Esta consulta me trae 501 identificadores de registros. Tengo 10 páginas de 50 registros listas y un registro más avisándome que hay más registros pero que no entran la paginación. No tiene mayor sentido colocar mas de 500 registros ya que el usuario rara vez navega mas allá de la página 6 o 7.


¿Entonces, como queda la consulta 2? Ésta queda muy extraña, por que tiene todos los campos del select y un where muy corto, solo para los joins y los identificadores seleccionados de la página (en nuestro caso 50 ids).


 


¿Voy a tener un procedimiento almacenado con 50 parámetros?, Si. Pero después de varios meses y muchas pruebas, no he notado problemas en esto ni una posible baja de rendimiento. Además, el tiempo que una aplicación demora en armar los 50 parámetros es ínfimo comparado con el tiempo que demora el sql en buscar los datos. Los mayores costos siempre están en el motor de datos. Es muy importante por esto mismo, que las tablas estén optimizadas e indexadas adecuadamente.


 


El segundo procedimiento queda entonces así. El parámetro @idempresa que ven es para utilizar mejor el índice de la tabla.


 


¿Dónde está la eficiencia en esta segunda consulta? El where es muy simple ya que utiliza sólo llaves primarias y foráneas y el select, a pasear de ser mucho más “pesado”, sólo trae 50 registros, minimizando los traspasos de información entre el motor y la aplicación.


 


Éste es un sistema de paginación muy eficiente para el manejo de los datos, pero tiene sus puntos en contra. Requiere más tiempo de desarrollo y también más mantención.


 


¿Cuando usarlo? No es para todas las consultas de una aplicación, pero para listados paginados donde hay mucha información, es 100% recomendable.


 


¿Que otras ventajas tiene con respecto a otros sistemas como un select dentro de otro select? En un ambiente transaccional con bloqueo de registros y tablas, estas consultas cortas y con pocos datos minimizan los tiempos de espera y la contención en la aplicación.


 


Y por último, ¿para que se utiliza el cache?, fácil. Para almacenar los 500 identificadores para cuando te pidan la siguiente página, en vez de bajar a buscar los siguientes 50 ids, los tomas de un caché.


Patrick Mac Kay.
Enero 2005.

Leave a Reply

Your email address will not be published. Required fields are marked *