de Martin LE TARNEC.

Lógica “inteligente”


Una de las características clave de las bases de datos SQL es su soporte para las sentencias ad hoc: en cualquier momento pueden ejecutarse nuevas sentencias. Esto sólo es posible porque el optimizador de sentencias (organizador de sentencias) actúa en tiempo de ejecución; analiza cada declaración cuando la recibe y genera inmediatamente un plan de ejecución razonable. La sobrecarga introducida por la optimización en el momento de la ejecución puede reducirse con las variables Bind.

El punto esencial de este resumen es que las bases de datos están optimizadas para SQL dinámico. Úsalo si se necesita.

Aun así, existe una práctica ampliamente conocida para evitar el uso de SQL dinámico en favor del SQL estático (generalmente se debe al mito según el cual el “SQL dinámico es lento”). Pero esta práctica es más negativa que positiva, sobre todo si la base de datos usa la “caché” de los planes de ejecuciones compartidos como DB2, Oracle o SQL Server.

Para el beneficio de esta demostración, imagina que una aplicación selecciona la tabla EMPLOYEES. La aplicación permite buscar por SUBSIDIARY_ID, EMPLOYEE_ID y LAST_NAME (insensible a mayúsculas y minúsculas) en cualquier combinación. También es posible escribir una sentencia sencilla para cubrir todos los casos usando una lógica “inteligente”.

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE ( subsidiary_id    = :sub_id OR :sub_id IS NULL )
   AND ( employee_id      = :emp_id OR :emp_id IS NULL )
   AND ( UPPER(last_name) = :name   OR :name   IS NULL )

La sentencia usa variables Bind con nombre para tener mayor claridad. Todas las expresiones de filtros posibles son programadas estáticamente en esta sentencia. Cada vez que un filtro sea innecesario, se usa solamente NULL en el lugar del criterio de búsqueda; se deshabilita la condición a través de la lógica del OR.

Es una sentencia SQL perfectamente razonable. El uso de NULL respeta las definiciones según la lógica de los 3, lógica avaluada del SQL. Sin embargo, es uno de los peores anti-patrones de rendimiento de todos.

La base de datos no puede optimizar el plan de ejecución para un filtro en particular porque ninguno de ellos podrá cancelarse en el momento de la ejecución. La base de datos necesita prepararse para el peor caso (si todos los filtros son deshabilitados):

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    2 |  478 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    2 |  478 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((:NAME   IS NULL OR UPPER("LAST_NAME")=:NAME) 
       AND (:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID) 
       AND (:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID))

Como consecuencia, la base de datos usa un escaneo entero de la tabla aunque existe un índice sobre cada columna .

No es que la base de datos no pueda resolver la lógica “inteligente”. Se crea el plan de ejecución genérico debido al uso de las variables Bind, y entonces se puede usar la “caché” y volver a utilizarlo más tarde con otros valores. Si no se usan variables Bind pero se escribe con los valores actuales dentro de la sentencia SQL, el optimizador seleccionará el índice correcto para el filtro activo:

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE( subsidiary_id    = NULL     OR NULL IS NULL )
   AND( employee_id      = NULL     OR NULL IS NULL )
   AND( UPPER(last_name) = 'WINAND' OR 'WINAND' IS NULL )
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |    1 |    2 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |    1 |    2 |
|*2 |   INDEX RANGE SCAN          | EMP_UP_NAME |    1 |    1 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("LAST_NAME")='WINAND')

Sin embargo, esta no es la solución. Eso solamente demuestra que la base de datos no puede resolver esas condiciones.

Aviso

Usar los valores literales hace vulnerable su aplicación a ataques de inyección de código SQL y puede causar problemas de rendimiento debido al incremento de gastos de optimización.

La solución evidente para las sentencias dinámicas es el SQL dinámico. De acuerdo con KISS principle (del inglés keep it simple, stupid!, es decir, ¡hazlo sencillo, estúpido!), manda solamente a la base de datos lo que se necesita ahora mismo -y nada más.

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE UPPER(last_name) = :name

Observa que la sentencia usa una variable Bind.

Sugerencia

Usa SQL dinámico si se necesitan filtros where dinámicos.

Utiliza variables Bind cuando se genera SQL dinámico. Si no, el mito de que el “SQL dinámico es lento” se convierte en realidad.

El problema descrito en esta sección es generalizado. Todas las bases de datos que usan la “caché” de los planes de ejecución compartido tienen alguna peculiaridad para enfrentarse con él, lo que generalmente agrega nuevos problemas y errores.

DB2

DB2 usa la “caché” de los planes de ejecuciones compartidos y está totalmente expuesto al problema descrito en esta sección.

DB2 permite especificar el método de la re-optimización usando el "hint" REOPT. Por defecto es NONE, lo que genera un plan de ejecución genérico y sufre el problema descrito. REOPT(ALWAYS) mandará al optimizador para siempre revisar a las variables Bind actuales y así producir el mejor plan por cada ejecución. Eso apaga eficazmente la “caché” de los planes de ejecución para esta sentencia.

La ultima opción es REOPT(ONCE) lo que revisará las variables Bind solamente durante la primera ejecución. El problema con este método es que su comportamiento no es determinista: los valores de la primera ejecución afectan a todas las ejecuciones. El plan de ejecución puede cambiar cada vez que la base de datos se reinicia o, menos previsiblemente, la “caché” del plan expira y el optimizador vuelve a crearlo usando diferentes valores para la siguiente vez que se ejecute.

MySQL

MySQL no sufre de este problema particular porque no tiene “caché” de los planes de ejecución. Una característica solicitada desde 2009 explica el impacto de la “caché” de los planes de ejecución. Parece que el optimizador de MySQL es bastante sencillo así que el plan de ejecución en la “caché” no vale la pena.

Oracle

La base de datos Oracle usa la “caché” de los planes de ejecución compartidos (“SQL Area”) y está completamente expuesta al problema descrito en esta sección.

Oracle introduce el llamado Bind peeking desde la versión 9i. “Bind peeking” habilita el optimizador para usar los valores actuales de los Bind de la primera ejecución cuando el plan de ejecución se está preparando. El problema con este método es que su comportamiento no es determinista: los valores de la primera ejecución afectan a todas las ejecuciones. El plan de ejecución puede cambiar cada vez que la base de datos se reinicia o, menos previsiblemente, la “caché” del plan expira y el optimizador vuelve a crearlo usando diferentes valores para la siguiente vez que se ejecute.

La version 11g introdujo adaptive cursor sharing para llevar más lejos la situación. Esta característica permite a la base de datos conservar varios planes de ejecución en la “caché” para la misma sentencia. Además, el optimizador evalúa a las variables Bind y almacena su selectividad estimada a lo largo del plan de ejecución. Cuando la “caché” se lee posteriormente, los valores de la selectividad de la variable Bind actual deben caer dentro de los rangos de la selectividad de los planes de ejecución en la “caché” para ser reutilizado. Si no, el optimizador crea un nuevo plan de ejecución y lo comparará con los planes de ejecución ya almacenados en la “caché” para esta sentencia. Si ya existe un plan de ejecución, la base de datos lo reemplaza con el nuevo plan de ejecución, que cubre también la selectividad estimada para los valores actuales de las variables Bind. Si no, agrega una nueva variante en la “caché” para esta sentencia, por supuesto, junto con la selectividad estimada.

PostgreSQL

La “caché” del plan de la sentencia trabaja solamente para las declaraciones abiertas, en curso (eso siempre y cuando se conserva el PreparedStatement abierto). El problema descrito arriba ocurre solamente cuando se vuelve a usar la sentencia tratada. Observa que el driver JDBC de PostgresSQL habilita la “caché” solamente después de la quinta ejecución. (ver también: Planear con los valores actuales de los Bind ).

SQL Server

SQL Server usa el llamado detector de parámetros(“parameter sniffing”). El detector habilita el optimizador para utilizar los valores actuales de los Bind de la primera ejecución durante la fase de análisis sintáctico. El problema con este método es que su comportamiento no es determinista: los valores de la primera ejecución afectan a todas las ejecuciones. El plan de ejecución puede cambiar cada vez que la base de datos se reinicia o, menos previsiblemente, la “caché” del plan expira y el optimizador vuelve a crearlo usando diferentes valores para la siguiente vez que se ejecute.

SQL Server 2005 agrega un nuevo “hint” para ganar más control sobre el detector de parámetros y la recompilación. El hint RECOMPILE reescribe el plan en la “caché” para la sentencia seleccionada. OPTIMIZE FOR permite la especificación de los valores de las variables actuales, que se usan solamente para la optimización. Por último, se puede proveer un plan de ejecución con el “hint” USE PLAN.

La implementación original del “hint” OPTION(RECOMPILE) tenía un error, asi que no se consideraban todas las variables Bind. La nueva implementación introducida con SQL Server 2008 tenía otro error, haciendo la situación muy confusa. Erland Sommarskog recolectó todas las informaciones relevantes para todas las versiones SQL Server.

Aunque los métodos heurísticos pueden mejorar el problema de la “lógica inteligente” para algunos casos, en realidad se construyeron para lidiar con los problemas de las variables Bind dentro de las conexiones con histogramas de columnas y las expresiones LIKE.

El método más fiable para llegar al mejor plan de ejecución es evitar los filtros innecesarios dentro de las sentencias SQL.

Si te gusta mi manera de explicar, te encantará mi libro.

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia