Los beneficios (desconocidos) de FETCH FIRST en Db2 para z/OS

Nota del editor: Este artículo fue publicado originalmente en inglés. Con la aprobación del autor, el equipo de IDUG ha creado la traducción que a continuación se presenta.

Vamos a comenzar esta discusión de manera sincera – Soy un gran fan de la cláusula de SQL FETCH FIRST n ROWS ONLY. Esta sentencia requiere un poco de clarificación debido a que existen varios casos de uso y otros que no hacen mucho sentido. De cualquier manera, voy a intentar el no complicar la discusión.

De manera simple – en cualquier momento que la aplicación necesita menos registros de los que califican para el query, o si hay un límite superior en el número de registros para ser devueltos a la aplicación, tiene sentido el incluir la cláusula FETCH FIRST n ROWS ONLY (en donde, n indica el número máximo de registros deseados).

Si su query pudiera seleccionar 10,000 registros (esto es – se obtienen todos los registros que calificaron en el predicado de WHERE), pero solamente se requieren 100 registros, entonces pueden utilizar la cláusula FETCH FIRST 100 ROWS ONLY. Si sólo se necesita 1 registro, entonces pueden especificar FETCH FIRST 1 ROW ONLY.

Si se detienen a pensarlo, esto provee información valiosa al Db2 sobre cómo optimizar su query. Si su query no contiene la cláusula FETCH FIRST y abre un cursor, después extrae 10 registros en la aplicación y cierra el cursor – Db2 no conoce cuántas extracciones (fetches) se hicieron en el código de la aplicación, Db2 sólo está intentando optimizar de manera individual cada sentencia SQL basándose en el texto del SQL. Esto podría ser un descubrimiento para los programadores y algunos DBAs de que Db2 no está inspeccionando el código de la aplicación. Incluir FETCH FIRST en el query provee de esta información a Db2.

Entonces, si un query selecciona y califica 10,000 registros, ¿existe algún beneficio en utilizar FETCH FIRST con un valor mayor que el número de registros seleccionados, por ejemplo, FETCH FIRST 99999 ROWS? Normalmente la respuesta es no – a menos que otras ejecuciones de este mismo query pudieran calificar un valor mayor que el utilizado en FETCH FIRST. La excepción se encuentra si el uso de FETCH FIRST permite el aprovechamiento del in-memory sort replacement (más de esto a continuación).

Resultados en el uso de cláusulas de FETCH FIRST vs OPTIMIZE FOR

¿Cuál es la diferencia entre FETCH FIRST 100 ROWS y OPTIMIZE FOR 100 ROWS?

OPTIMIZE FOR 100 ROWS le indica al Optimizer que “optimice” para dicho número. Pero si especifican OPTIMIZE FOR 100 ROWS, Db2 no limita el número de registros que se pueden obtener – si su query califica 10,000 registros y ustedes especifican OPTIMIZE FOR 100 ROWS – Db2 permitirá extraer el registro 101, 102 y continuar hasta el límite de registros calificados para este query (SQLCODE +100).

Selección de access paths en FETCH FIRST vs OPTIMIZE FOR

Para la selección del access path (esto es, informar al Optimizer que optimice para 100 registros como en los ejemplos anteriores), ambas cláusulas trabajan de manera idéntica y pueden ser utilizadas juntas o independientemente. Para efectos de optimización, Db2 utilizará el valor de OPTIMIZE FOR en caso de que haya sido utilizado, de otra manera si solo se encuentra FETCH FIRST, entonces dicho valor es utilizado implícitamente como un OPTIMIZE FOR. Si se preguntan si es posible especificar diferentes valores para esto, sí, sí es posible. La siguiente sección elabora sobre si tiene sentido o no.

Recuerden, la meta de FETCH FIRST es limitar el número de registros que pueden ser enviados a la aplicación. Mientras que, el uso en conjunto de OPTIMIZE FOR n ROWS y FETCH FIRST solamente es necesario si el access path deseado no es seleccionado con el uso de FETCH FIRST por sí solo. Uno (1) es el número mágico a recordar ya que FETCH FIRST 1 ROW ONLY u OPTIMIZE FOR 1 ROW pueden sugerir fuertemente al Optimizer para elegir un access path que evite un sort. Otros valores (mayores a 1) para FETCH FIRST u OPTIMIZE también pueden sugerir al Optimizer que evite un sort si es posible – pero esta “sugerencia” no es tan fuerte como FETCH FIRST 1 ROW u OPTIMIZE FOR 1 ROW. Entonces, si utilizan FETCH FIRST 100 ROWS ONLY y ustedes desean evitar un sort (pero inicialmente no fue posible), entonces pueden intentar agregar OPTIMIZE FOR 2 ROWS. Otras variantes o diferentes valores para estas cláusulas son menos comunes y poco probable de obtener comportamientos repetibles (o lógicamente explicables).

 

Beneficios en performance con FETCH FIRST

Cuando el access path para una sentencia SQL no incluye sorts o materializaciones (si un resultado intermedio es escrito en temporary workfile) podría no haber beneficio en el performance al incluir FETCH FIRST. Cuando hay sorts o materializaciones, entonces podría haber un incremento significante en el performance dependiendo de qué tan grande es el número de registros calificados en comparación con qué tan pequeño es el valor usado en FETCH FIRST.

Db2 comenzó a optimizar el performance del sort para FETCH FIRST en Db2 v9, y la ilustración 1 y 2 demuestran esta optimización inicial.

purcell-espanol-1.png

Ilustración 1. SQL con sort y número de extracciones (fetches) conocidas únicamente por la aplicación

 

El ejemplo en la ilustración anterior muestra una sentencia SQL que requiere un sort para el ORDER BY y la aplicación sólo obtiene 3 registros del resultado ordenado. Aunque el resultado consiste de sólo 10 registros, la burbuja de la derecha cuestiona - ¿qué tal si habrían sido 1 millón de registros? En cuyo caso la respuesta sería que el sort habría necesitado escribir el resultado intermedio a workfile – incluyendo el conjunto completo de 1 millón de registros ya que Db2 no tiene conocimiento de que un número distinto a 1 millón de registros sea requerido por la aplicación (y de manera ordenada vía el sort).

purcell-espanol-2.png

Ilustración 2. Ordenamiento (sort) con FETCH FIRST.

 

En la ilustración 2, el query contiene las cláusulas ORDER BY C1 y FETCH FIRST 3 ROWS ONLY. La optimización que se muestra fue presentada en Db2 9 para z/OS y se conoce como “in-memory replacement sort”. En lugar de ordenar los 10 registros (o 1 millón de registros) – mientras los registros son escaneados, Db2 solamente mantiene en memoria (in-memory) los top 3 registros (esto es debido a que la aplicación utilizó FETCH FIRST 3 ROWS ONLY). Las flechas en rojo muestran de izquierda a derecha qué registros se mantienen “in-memory” y finalmente cuáles son enviados a la aplicación. Mientras los datos son escaneados, los top 3 registros son inicialmente los 1er 3 registros leídos (9, 6 y 4). Cuando el valor 1 es leído, reemplaza el 9, luego 3 reemplaza 6, 2 reemplaza 4, y finalmente los valores 1, 2 y 3 son enviados como los primeros 3 registros (como fue solicitado por el query).

La operación de “in-memory sort replacement” se utiliza solamente cuando se requiere un sort para un query con FETCH FIRST y el resultado se puede manejar en modo in-memory. A diferencia de otros sorts de Db2, in-memory sort replacement no puede ser vaciado a disco y de ahí que se debe garantizar que pueda ser manejado en memoria. FETCH FIRST garantiza el máximo número de registros a ser obtenidos y la cantidad de memoria requerida es conocida en base a la longitud total de las columnas seleccionadas (y ordenadas) y el valor de FETCH FIRST. Esta técnica in-memory generalmente tiene mejor performance que un sort regular de Db2 donde se requiere un número de registros menor a 1000 y Db2 (hasta la fecha) indica el almacenamiento de memoria para este sort en 128K de memoria.

En Db2 11, hay optimizaciones adicionales para el sort con FETCH FIRST aún si el sort no puede ser alojado in-memory. Para un número muy grande de registros calificados, el sort podría necesitar numerosas “pasadas” (conocidas como merge passes) para ordenar el resultado completo. En aquellos casos con FETCH FIRST, cada fase intermedia de sort puede limitar el resultado intermedio al valor del FETCH FIRST. Lo que esto significa es que, si ustedes están ordenando 100 millones de registros, hay un beneficio en conocer que la aplicación solo necesita el top 1000 o 10,000 (o incluso 1 millón) de registros.

Db2 12 para z/OS ofrece un extensivo número de optimizaciones del performance para el sort (y eliminación de sorts) con FETCH FIRST.

 

Optimizaciones para FETCH FIRST en Db2 para z/OS

La primera mejora para FETCH FIRST en Db2 12 es la habilidad para reubicar las cláusulas ORDER BY y FETCH FIRST en bloques materializados (materialized query blocks).

purcell-espanol-3.png

Ilustración 3. Reubicación de ORDER BY y FETCH FIRST

 

El ejemplo anterior muestra la definición de una vista que contiene tres tablas combinadas mediante UNION ALL, y posteriormente un SELECT hacia esa vista con un query que incluye ORDER BY y FETCH FIRST. Anteriormente a Db2 12, las operaciones de ORDER BY y FETCH FIRST habrían sido aplicadas a todos los registros obtenidos de la vista. Como lo indica el ejemplo, Db2 12 reubica las secciones de ORDER BY y FETCH FIRST en cada bloque del query que ahora va a permitir la técnica de in-memory replacement para ser utilizada de manera separada para cada sort o uso de índice para evitar dicho sort en caso de que existiera algún índice que aplique.

La habilidad de reducir el tamaño del sort para cada UNION ALL o para el query completo no habría sido posible sin el uso de FETCH FIRST en el query del ejemplo.

La segunda mejora relacionada en Db2 12 puede reducir el número de registros obtenidos hacia el sort si un índice puede provee ordenamiento para la(s) columna principal del sort, como lo indica la siguiente ilustración.

purcell-espanol-4.png

Ilustración 4. Orden parcial con FETCH FIRST

 

El ejemplo es un SELECT simple con ORDER BY y FETCH FIRST. Para que Db2 evite realizar un sort para satisfacer el ORDER BY, el Optimizer debe elegir un índice con columnas que coincidan con aquellas de la cláusula ORDER BY. En este ejemplo, el ORDER BY es ejecutado sobre las columnas C1, C2, pero solamente existe un índice sobre C1 – de manera que un sort es necesario para ordenar. Antes de Db2 12, todos los registros que calificaran habrían sido ordenados y posteriormente el top 10 sería extraído (utilizando FETCH FIRST 10 ROWS ONLY). Sin embargo, en Db2 12, el Optimizer puede reconocer que, al elegir el acceso vía el índice sobre C1, el orden se obtiene a través de dicha columna. Lo cual quiere decir que, una vez que 10 registros han sido extraídos, Db2 12 recuerda extraer hasta el siguiente cambio en el valor de C1 – En el ejemplo, una vez que el décimo registro se extrae, Db2 continuará la extracción hasta el registro número 13 cuando C1 cambia de valor 4 a 5. La operación de sort solamente necesita ordenar el top 12 sobre el orden C1, C2 y entonces regresar 10 registros.

Esta optimización sólo está disponible a través del uso de FETCH FIRST.

 

Otras optimizaciones (conocidas) con FETCH FIRST

La cláusula de FETCH FIRST ha estado disponible desde hace varias versiones de Db2, pero es probable que muchos de estos beneficios de performance sean desconocidos para la mayoría de los usuarios. Existen muchas otras optimizaciones con los que los usuarios pueden estar más familiarizados.

Primeramente, cuando utilizan FETCH FIRST en un query distribuido, Db2 puede explotarlo mediante fast implicit close para mejorar el performance. El proceso fast implicit close consiste en cerrar el cursor después de haber extraído el registro n o cuando no existan más registros (SQLCODE=+100). Esto puede mejorar el performance al ahorrar una transmisión de red adicional entre el cliente y servidor para solicitar que se cierre el cursor.

Otra optimización conocida de FETCH FIRST, o más específico, un uso común de FETCH FIRST que puede mejorar el performance es FETCH FIRST 1 ROW ONLY en un SELECT del tipo singleton como se muestra a continuación.

purcell-espanol-5.png

Ilustración 5. FETCH FIRST en un SELECT singleton.

 

Si se utiliza un SELECT singleton para revisar o confirmar que al menos 1 registro coincide, FETCH FIRST puede ser utilizado para mejorar la eficiencia de este SELECT. Si es una garantía que el query va a regresar 1 registro (mediante un predicado que coincide con un índice único), entonces el uso de FETCH FIRST no es necesario. Sin embargo, en el ejemplo anterior se muestra un caso en el que agregar FETCH FIRST 1 ROW ONLY mejora el desempeño. El query de la izquierda va a extraer el primer registro, y después Db2 buscará el segundo registro para revisar la condición del SQLCODE=0 o -811 (el cual es un error que indica que más de un registro fue devuelto en un SELECT singleton). Debido a que el query solamente revisa si uno o más registros existen, agregar FETCH FIRST 1 ROW ONLY indica que Db2 se va a detener una vez que obtenga el primer registro y no buscará un segundo. Esta sugerencia puede mejorar el performance para aquellos SELECTs que sólo requieren 1 registro.

Otro uso común (y documentado) para FETCH FIRST es utilizarlo en subqueries donde se requiere un número de registros limitado. Esto no es necesario para subqueries con funcionas en columnas como MAX, MIN, COUNT, etc. El ejemplo es interesante pero limitado.

 

Resumen y optimizaciones futuras para FETCH FIRST

La finalidad aquí es mostrar los beneficios potenciales en performance a través del uso de FETCH FIRST en un query cuando la aplicación requiere un subconjunto de los registros encontrados. Un escenario adicional es cuando la aplicación ubica un límite superior en el número de registros que pueden ser obtenidos. En dicha situación, si la aplicación tiene la necesidad de extraer una cantidad de registros mayor a aquel límite superior del FETCH FIRST utilizado, entonces hablar sobre realizar scrolling en un cursor o paginación para extraer el siguiente bloque de registros es una discusión para otro momento.

Y mientras que hemos mencionado algunas de las optimizaciones para reducir el costo de materializaciones y sorts en versiones recientes de Db2 con FETCH FIRST, pueden esperar que habrá nuevas optimizaciones en el futuro que van a aprovechar el conocimiento del Db2 sobre el máximo número de registros que pueden ser regresados por el query.

 

Biografía: Terry Purcell es el líder del desarrollo del Optimizer de Db2 para z/OS en el laboratorio de Silicon Valley de IBM, y es mundialmente reconocido por su experiencia y conocimientos en optimización de performance y queries en Db2 para z/OS.

Recent Stories
IBM DB2 EN LA NUBE DE AMAZON AWS

JSON SQL APIs en Db2 para z/OS y servicios nativos REST

Un nuevo capítulo de IDUG en México