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.

Paginación de registros V 1.0



Ayer jueves 5 de agosto, luego de una decepcionante charla que presenciamos junto a unos amigos (Leonardo Garcés y Luís Hereira), decidimos ir a pasar los malos ratos vividos al lugar tradicionalmente utilizado para celebrar, el ShopDog de Pedro de Valdivia con Providencia u 11 de septiembre.


La conversación derivó en variados temas, y después de algunas discusiones bizantinas, caímos en la eterna discusión sobre métodos de paginación. Yo presenté el esquema que actualmente utilizamos en IConstruye. 


Nuestro sistema de paginación funciona de la siguiente forma. Es necesario tener un campo identity y utilizar cache. Una consulta a paginar típica tiene los siguientes elementos


Select
      campos
From
      tablas
Where
      cláusulas


La idea consiste en dividir esta consulta en dos consultas, una que ejecute principalmente la parte del where y la segunda que ejecute principalmente la parte del select y los campos.


Con la primera consulta acotamos a una lista de ids (identitys) de los documentos que cumplen con la restricción where (por eso digo que principalmente ejecuta el where), y almacenamos nuestros N ids en un caché. Si consideramos que cada int pesa 4bytes y almacenamos 500 elementos, tenemos 2kilos (mas algunos bytes de estructura y otros) en un caché. No es mucho. Es mejor que almacenar todo en un caché.


Con la segunda consulta, le pedimos una página cualquiera de x registros. Con esto obtengo los x ids moviéndome sobre el arreglo de enteros almacenado en el cache. Con los ids, ejecuto la segunda consulta, que es mucho mas pesada en el select (y liviana en el where), pero como el resultado son sólo los documentos que quiero, se minimiza bastante el trafico de información que no va a ser considerada después. Traigo así SOLO los x registros de la página solicitada. Para cualquier otra página, ya tengo los ids almacenados en el cache así que no es necesario ejecutar la primera consulta.


Luís menciona que mi sistema está representado por la ecuación Y = m*X + b, es decir una recta con pendiente m y que parte en b. Pueden ver la discusión escrita en la servilleta en la imagen asociada. El costo b es el de la consulta 1 (la de los ids) y por cada pagina X se le pondera por un costo m. Yo argumento que m tiende a cero ya que no hay diferencia significante entre la primera y la última página.



Servilleta de la discusión 


Cuando la discusión se torna inútil nuevamente, Luís propone su método de paginación, obtenido desde http://www.winnetmag.com/Article/ArticleID/40505/40505.html.


Este método se basa en dos consultas, una de ellas anidada en la otra. Un seudo SQL sería algo como


SELECT TOP page_size *
FROM table
WHERE primary_key NOT IN
      (SELECT TOP page_size * (page_number – 1) primary_key FROM table WHERE filter_conditions ORDER BY sort_field)
AND filter_criteria
ORDER BY sort_field


El argumento mío no se deja esperar. Siempre haces dos consultas. Su respuesta, buena en todo caso, es que las primeras páginas son muy rápidas de cargar (por que el select anidado da Top 0 para el primer caso) y que el usuario nunca avanza muchas páginas. Es cierto, pero también ocurre a veces que usuarios llegan hasta altas páginas y ya deja de funcionar bien. En IConstruye utilizamos ese mismo sistema durante un tiempo y habían usuarios que llegaban hasta la página 27.


Cuando la página es muy alta, se pone cada vez mas lento. Este sistema es mas parecido a una exponencial. Verán en la imagen la exponencial y su ecuación ex. Interesante es encontrar el punto donde ex se junta con m*x+b.


¿Cual es mejor?. Dependerá de los requerimientos. Probablemente el sistema perfecto sea una mezcla de ambos, tal como existe el HybridDictionary, que tiene un comportamiento dual dependiendo de la cantidad de elementos. También recuerdo dentro de los algoritmos de ordenamiento, donde el QuickSort era muy rápido, pero para pocos elementos era muy lento. Si se hacia un algoritmo hibrido entre QuickSort y BubbleSort (creo que era el otro), para pocos elementos funcionaba bien el Bubble y para muchos, el Quick.