Sentencias con variables

Esta sección aborda un tema que se omite en muchos libros de SQL: sentencias con variables y variables Bind.

Las variables Bind (también llamadas parámetros dinámicos o parámetros Bind) son una alternativa para presentar los datos a la base de datos. En lugar de poner los valores directamente dentro de la sentencia SQL, se puede usar solamente un marcador de posición como ?, :name o @name y proveer el valor actual usando una llamada independiente a un API.

No tiene nada malo escribir los valores directamente en las sentencias; existe, sin embargo, dos buenas razones de usar las variables Bind dentro de sus programas:

Seguridad

Las variables Bind son la mejor manera para prevenir una inyección de código SQL.

Rendimiento

Las bases de datos con un “caché” del plan de ejecución como SQL Server y la base de datos Oracle, pueden reutilizar un plan de ejecución cuando la misma sentencia se ejecuta varias veces. Así se ahorra el esfuerzo de volver a construir absolutamente lo mismo. Si se ponen valores diferentes dentro de la sentencia SQL, la base de datos lo toma en cuenta como una sentencia diferente y crea un nuevo plan de ejecución.

Cuando se usan las variables Bind, no se escribe el valor actual pero en su lugar, se inserta un marcador de posición dentro de la sentencia SQL. De esta manera, no cambia la sentencia cuando se ejecuta con valores diferentes.

Por supuesto, existen excepciones, como por ejemplo, si el volumen de datos afectados depende del valor actual:

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20

99 rows selected.

----------------------------------------------------------------
|Id | Operation                   | Name         | Rows | Cost |
----------------------------------------------------------------
| 0 | SELECT STATEMENT            |              |   99 |   70 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |   99 |   70 |
|*2 |   INDEX RANGE SCAN          | EMPLOYEES_PK |   99 |    2 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SUBSIDIARY_ID"=20)

Una búsqueda (“LOOKUP”) del índice proporciona los mejores rendimientos para pequeños rangos de ID secundarios, pero un TABLE ACCESS FULL puede superar al índice para grandes rangos:

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 30

1000 rows selected.

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SUBSIDIARY_ID"=30)

En este caso, el histograma sobre SUBSIDIARY_ID cumple este objetivo. El optimizador lo usa para determinar la frecuencia de los ID secundarios indicados dentro de la sentencia SQL. Entonces, trae dos estimaciones del número de registros diferentes, uno por cada sentencia.

El cálculo del costo posterior resultará, por lo tanto, en dos valores diferentes del costo. Por último, cuando el optimizador, selecciona el plan de ejecución, tomará siempre el que tiene el costo más bajo. Para el rango pequeño del ID secundario, es el que usa el índice.

El costo de la operación TABLE ACCESS BY INDEX ROWID está altamente vinculado a la estimación del número de registros. Seleccionar diez veces más registros elevará el valor del costo por este mismo factor. El costo general usando un índice es más alto que un escaneo completo de la tabla. El optimizador seleccionará por lo tanto el otro plan de ejecución para los rangos más grandes de ID secundarios.

Cuando usas las variables Bind, el optimizador no tiene valores concretos disponibles para determinar su frecuencia. Se asume una distribución equitativa y siempre devuelve el mismo número de registros estimados y valores del costo. Al final, el optimizador seleccionará siempre el mismo plan de ejecución.

Si se compara un optimizador con un compilador, las variables Bind son como variables del programa, pero si se escriben los valores directamente dentro de la sentencia SQL, los considera como constantes. La base de datos puede usar los valores desde la sentencia SQL durante la optimización de la misma manera que un compilador evalúa las expresiones contantes durante la compilación. Las variables Bind son, básicamente, invisibles para el optimizador así como los valores de las variables en tiempo de ejecución son ignorados por el compilador.

Desde esta perspectiva, es un poco paradójico que las variables Bind puedan mejorar el rendimiento, cuando sin usarlas, el optimizador siempre escogerá el mejor plan. Pero la pregunta es, ¿a qué precio? Generar y evaluar todas las variantes de los planes de ejecución es un esfuerzo gigante que no se amortiza si al final, se obtiene el mismo resultado.

Decidir entre la construcción de un plan de ejecución especializado o uno genérico presenta un dilema para la base de datos. ¿Cuánto esfuerzo exige evaluar todas las variantes de los planes de ejecución por cada ejecución para siempre escoger el mejor plan de ejecución, así como evaluar el costo de recursos de la optimización para guardar el plan de ejecución en la “caché”, sabiendo que se volverá a usar mientras sea posible, aceptando el riesgo de usar un plan de ejecución no adecuado? El dilema es que la base de datos no sabe el ciclo completo de la optimización entregará un plan de ejecución diferente hasta que la optimización completa esté terminada. Los fabricantes de base de datos intentan solucionar este dilema con métodos heurísticos, pero con éxitos muy limitados.

Como desarrollador, se pueden utilizar las variables Bind intencionalmente para intentar resolver este problema. Es decir, se puede siempre usar las variables Bind excepto para valores que puedan influir en el plan de ejecución.

Consideremos por ejemplo los valores para los estados “hecho” y “por hacer”, que típicamente están distribuidos de manera desigual. El número de entradas “hecho” excede generalmente los registros “por hacer” por una gran magnitud. Usar un índice tiene sentido sólo cuando se busca entre las entradas “por hacer”. Otro caso interesante sucede con el particionado, cuando se dividen tablas e índices en diferentes zonas de almacenamiento. El valor actual puede influir sobre qué particiones serán escaneadas. El rendimiento de las sentencias con LIKE pueden sufrir con las variables Bind tal y como se verá en la siguiente sección.

El siguiente fragmento de código muestra cómo usar las variables Bind en varios lenguajes de programación.

El signo de interrogación (?) es el único marcador de posición de caracteres que está definido en los estándares SQL. El signo de interrogación es un parámetro de posición. Eso significa que los signos de interrogación se cuentan desde la izquierda hacia la derecha. Para ligar una variable a un signo de interrogación particular, se debe especificar su número. Y aunque se puede usar tal cual, lo cierto es que no es muy práctico, este método dado que la numeración cambia cuando agregas o quitas un marcador de posición. Algunas bases de datos ofrecen una extensión propietaria para identificar los parámetros y así resolver el problema - por ejemplo, usan el símbolo “arroba” (@name) o dos puntos (:name).

String sql = prepare("SELECT * FROM ? WHERE ?");

sql.execute('employees', 'employee_id = 1');

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