El SQL Dinámico Es Lento

La verdadera esencia del mito “el SQL Dinámico es Lento” es bastante simple; SQL dinámico puede ser lento cuando se hace mal.

El problema es que el SQL dinámico es frecuentemente utilizado por las razones equivocadas, a veces sin saberlo. Para aclarar la confusión, voy a utilizar los siguientes términos:

SQL Embebido

Embeber SQL directamente en el código fuente de un programa es bastante común en las bases de datos procedimentales como Oracle PL/SQL o Microsoft Transact-SQL. También es posible embeber SQL en otros lenguajes como C.

El beneficio de SQL embebido es la transición fluida con el respectivo lenguaje de programación. Sin embargo, SQL embebido es compilado en el programa. No puede cambiar en tiempo de ejecución, es estatico.

SQL Dinámico

SQL Dinámico es manejado como una cadena en la aplicación. La aplicación puede cambiar la cadena SQL en tiempo de ejecución antes de pasarla a la capa de base de datos. De hecho, es la manera más común de acceder a las bases de datos.

SQL Estático

Uso este término SQL estático para describir instrucciones SQL que no cambian en tiempo de ejecución, ya sea SQL embebido que no puede cambiar en tiempo de ejecución o SQL dinámico que podría cambiar, pero no lo hace.

La clave de estas definiciones que es una sentencia SQL puede ser dinámica y estatica al mismo tiempo. En otras palabras, existen distintos niveles de SQL dinámico. Considere los ejemplos siguientes:

String sql = "SELECT first_name, last_name"
           + "  FROM employees"
           + " WHERE employee_id = " + employeeId;

ResultSet rs = con.executeQuery(sql);

¿Esto SQL dinámico? De acuerdo con la definición anterior, lo es. La sentencia SQL es preparada como una cadena y se pasa a la capa de base de datos. Pero, ¿Es también SQL estático? Asumiendo que el valor de la variable employeeId, cambia, entonces no es SQL estático porque la cadena SQL cambia en tiempo de ejecución. Este es un ejemplo de SQL dinámico que afectaría el rendimiento. El problema no es que sea SQL dinámico, sino que no se utiliza variables bind. Por ejemplo, el signo de interrogación ? o :name, son marcadores de posición para valores que cambian durante la ejecución. Eso significa que, el ejemplo puede convertir SQL estático utilizando variables bind en lugar del valor de la variable employeeId.

Un uso razonable de SQL dinámico consiste en cambiar la estructurade una sentencia en tiempo de ejecución. Esto es algo que no se puede hacer con variables bind. Por ejemplo, una cláusula where condicional:

String where = "";
if (subsidiaryId != null) {
   where += (where == "") ? " WHERE " : " AND " 
         +  "subsidiary_id = " + subsidiaryId;
}
if (employeeId != null) {
   where += (where == "") ? " WHERE " : " AND " 
         +  "employee_id = " + employeeId;
}
if (lastName != null) {
   where += (where == "") ? " WHERE " : " AND " 
         +  "UPPER(last_name) = '"+lastName.toUpperCase()+"'";
}
String SQL = "SELECT employee_id, first_name, last_name "
           + "  FROM employees" 
           +   where;
// execute SQL

El código construye una sentencia SQL para recuperar los datos de los empleados de acuerdo con tres criterios de filtrado. Aunque resulte engorroso, el SQL generado puede ser ejecutado utlizando el mejor índice disponible. No obstante, este enfoque es problemático debido a la posible vulnerabilidad deinyección SQL y al alto costo de optimización: la base de datos tiene que recrear el plan de ejecución cada vez, porque los términos de búsqueda —que pueden ser diferente en cada ocasión— impiden el uso de la caché. Sentencias con variables explica en detalle este costo de optimización. De nuevo, SQL dinámico no es el problema, sino el hecho de no utilizar las variables bind.

Se omite un ejemplo que construya la cláusula where dinámicamente y utilice variables bind porque resulta aun más engorroso que el ejemplo anterior. Sin embargo, la mayoría de frameworks ORM ofrecen una manera suficientemente práctica para crear SQL dinámico utilizando variables bind. A continuación se muestran algunos ejemplos:

Utilizar SQL dinámico con variables bind permite que el optimizador elija el mejor plan de ejecución para la combinación de las sentencias where. Esto proporcionará un mejor rendimiento que las construcciones descritas en Lógica “inteligente”:

SELECT first_name, last_name
  FROM employees
 WHERE (     employee_id = ? OR ? IS NULL)
   AND (   subsidiary_id = ? OR ? IS NULL)
   AND (UPPER(last_name) = ? OR ? IS NULL)

La razón por la que SQL dinámico es lento, suele ser, en muchos casos, que no se utilizan variables bind; es decir, utilizar SQL dinámico por la razón equivocada.

Sin embargo, hay algunos casos —diría que raros— en los cuales SQL dinámico puede ser más lento que un "smart logic", como el descrito anteriormente. Esto ocurre cuando se ejecutan sentencias SQL muy baratas (rápidas) que se ejecutan en una frecuencia muy alta. Pero, antes que nada, existen dos términos más por explicar:

Análisis Sintáctico Completo

El análisis sintáctico completo consiste en construir un plan de ejecución a partir de la sentencia SQL. Se trata de un proceso que requiere un esfuerzo mayor; inspeccionar todas las instrucciones SQL; considerando todas las ordenes de unión, etc. El análisis sintáctico completo consume muchos recursos.

Análisis Sintáctico Ligero

El análisis sintáctico ligero consiste en buscar, y utilizar un plan de ejecución almacenado en caché. Se realizan algunas comprobaciones menores, como los derechos de acceso, pero el plan de ejecución se puede utilizar tal cual. Se trata de una operación rápida.

La clave de la caché es básicamente la cadena SQL literal —normalmente un hash de la misma. Si no hay una coincidencia exacta, se activa un análisis completo. Es por eso que los literales en línea —A diferencia de los parámetros bind— activan un análisis completo, a menos que se vuelvan a utilizar los mismos términos. Pero incluso en ese caso, existe una alta posibilidad que la ejecución del plan de ejecución anterior ya haya expirado de la caché, ya que constantemente se están generando nuevos.

Sin embargo, existe una manera de ejecutar una sentencia sin tener que realizar ningún tipo de análisis sintáctico —ni siquiera uno ligero. El truco consiste en mantener abierta la sentencia analizada, por ejemplo, como en el siguiente pseudocódigo Java:

PreparedStatement pSQL = con.prepareStatement("select ...");
for (String last_name:last_names) {
    pSQL.setString(1, last_name.toUpperCase());
    ResultSet rs = pSQL.executeQuery();
    // process result
}
pSQL.close();

Nótese que el PreparedStatement se abre y se cierra solo una vez —aunque puede ejecutarse muchas veces. Eso significa que solo un hay una operación de análisis sintáctico —durante la preparación— pero ninguna dentro del bucle.

El inconveniente es que al convertir la instrucción en SQL dinámico, la llamada a prepareStatement pasa a realizarse dentro del bucle —causando un análisis ligero— en cada ejecución. La sobrecarga que supone este análisis, que también pueden incluir latencias en la red, pueden superar el ahorro de un mejor plan de ejecución cuando la instrucción ejecutada con frecuencia y de todos modos es rápida. Esto es especialmente cierto si el plan de ejecución actual no varía entre las distintas cláusulas where. por ejemplo, porque siempre está presente una cláusula WHERE bien indexada.

Aunque el truco de "preparar antes del bucle" se utiliza de forma explícita, es muy habitual en procedimientos almacenados, pero de manera implícita. Lenguajes como PL/SQL —con SQL estático real— preparan el SQL cuando el procedimiento es compilado o, como mucho, una vez por ejecución. Cambiar eso por SQL dinámico pueden afectar seriamente el rendimiento.