de Martin LE TARNEC.

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
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |   99 |   70 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |   99 |   70 |
|*2 |   INDEX RANGE SCAN          | EMPLOYEE_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
----------------------------------------------------
| 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.

Sugerencia

Los histogramas para las columnas funcionan mejor cuando los valores no están distribuidos de forma uniforme.

Para columnas con distribución uniforme, generalmente es suficiente con dividir el número de valores distintos por el número de registros en la tabla. Ese método funciona también usando las variables Bind.

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.

Sugerencia

No usar las variables Bind es como recompilar continuamente el programa.

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.

Sugerencia

En realidad, existen pocos casos en los que el valor actual afecta al plan de ejecución.En el caso general, deberían usarse las variables Bind por defecto, aunque sólo sea para prevenir la inyección de código SQL.

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

C#

Sin variables Bind:

int subsidiary_id;
SqlCommand cmd = new SqlCommand(
                   "select first_name, last_name" 
                 + "  from employees"
                 + " where subsidiary_id = " + subsidiary_id
                 , connection);

Con variables Bind:

int subsidiary_id;
SqlCommand cmd =
       new SqlCommand(
                      "select first_name, last_name" 
                    + "  from employees"
                    + " where subsidiary_id = @subsidiary_id
                    , connection);
cmd.Parameters.AddWithValue("@subsidiary_id", subsidiary_id);

Ver también la documentación de la clase: SqlParameterCollection .

Java

Sin variables Bind:

int subsidiary_id;
Statement command = connection.createStatement(
                    "select first_name, last_name" 
                  + "  from employees"
                  + " where subsidiary_id = " + subsidiary_id
                  );

Con variables Bind:

int subsidiary_id;
PreparedStatement command = connection.prepareStatement(
                    "select first_name, last_name" 
                  + "  from employees"
                  + " where subsidiary_id = ?"
                  );
command.setInt(1, subsidiary_id);

Ver también la documentación de la clase: PreparedStatement .

Perl

Sin variables Bind:

my $subsidiary_id;
my $sth = $dbh->prepare(
                  "select first_name, last_name" 
                . "  from employees"
                . " where subsidiary_id = $subsidiary_id"
                );
$sth->execute();

Con variables Bind:

my $subsidiary_id;
my $sth = $dbh->prepare(
                  "select first_name, last_name" 
                . "  from employees"
                . " where subsidiary_id = ?"
                );
$sth->execute($subsidiary_id);

Ver: Programar en DBI Perl .

PHP

Usar MySQL, sin variables Bind:

$mysqli->query("select first_name, last_name" 
             . "  from employees"
             . " where subsidiary_id = " . $subsidiary_id);

Con variables Bind:

if ($stmt = $mysqli->prepare("select first_name, last_name" 
                           . "  from employees"
                           . " where subsidiary_id = ?")) 
{
   $stmt->bind_param("i", $subsidiary_id);
   $stmt->execute();
} else {
  /* handle SQL error */
}

Ver también la documentación de la clase mysqli_stmt::bind_param y "Sentencias preparadas y procedimientos almacenados" en la documentación PDO.

Ruby

Sin variables Bind:

dbh.execute("select first_name, last_name" 
          + "  from employees"
          + " where subsidiary_id = #{subsidiary_id}");

Con variables Bind:

dbh.prepare("select first_name, last_name" 
          + "  from employees"
          + " where subsidiary_id = ?");
dbh.execute(subsidiary_id);

Ver también: "Citar, marcadores de posiciones, y variables Bind" en el tutorial DBI .

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).

Nota

Las variables Bind no pueden cambiar la estructura de la sentencia SQL.

Eso significa que no se pueden usar las variables Bind para el nombre de las tablas o columnas. Las siguientes variables Bind no funcionan:

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

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

Si se necesita cambiar la estructura de una sentencia SQL durante la fase de ejecución, debe usarse SQL dinámico.

Cursor compartido o parametrización forzada

Cuanto más complejo sean el optimizador y la consulta SQL compilada, más importante resulta la "caché" del plan de ejecución. Tanto SQL Server como la base de datos Oracle tienen características que reemplazan de forma automática los valores literales dentro de la sentencia SQL con variables Bind. Esta característica se denomina Cursor Compartido ("CURSOR_SHARING") en Oracle y parametrización forzada en SQL Server.

Ambas características son métodos alternativos para las aplicaciones que no soportan al 100% las variables Bind. Habilitar estas características evita que los desarrolladores usen a propósito valores literales.

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

Sugerencia

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