La generación de código dinámico, tan útil como puede ser en algunas ocasiones, puede generarnos problemas de rendimiento en muchos casos. En la mayor parte de estas ocasiones el problema viene derivado del número de compilaciones que infligimos al sistema. Obviamente el impacto de estas compilaciones dependerá mucho de lo “cargado” que esté el sistema en este aspecto, de la cantidad de CPU libre, de la complejidad del código a compilar, la complejidad del modelo de datos, la velocidad de la CPU, etc.
Antes de plantearnos un código puramente dinámico debemos pensar si existen alternativas viables para “convertir a estático” de forma completa o parcial. De forma completa eliminaríamos totalmente el código dinámico sustituyéndolo por código estático que funcionalmente tenga el mismo comportamiento. En otros casos no es posible (o no es factible por el coste, rendimiento, etc.) tomar este enfoque. En estos casos una técnica que habitualmente utilizamos es bifurcar la lógica de forma que los casos más habituales pasen a ser procedimientos almacenados estáticos independientes y dejar la parte dinámica para los casos especiales y poco frecuentes. Con esta alternativa si los casos más frecuentes son el 95% nos estaremos eliminando de un plumazo el 95% de las compilaciones generadas por este código.
Existen otros casos donde el dinamismo afecta a por ejemplo los objetos (tablas, vistas, etc.) implicadas en las consultas. En estos casos las alternativas anteriores no suelen ser válidas. Para este tipo de escenarios vamos a comentar una alternativa basada en el cacheo automático de código dinámico. En este ejemplo vamos a ver un caso de creación de tabla e inserción de datos donde el nombre del objeto debe ser necesariamente dinámico.
Un ejemplo típico de este estilo de procedimientos sería el siguiente:
create procedure pa_insert_dinámico (@a int, @b int, @datos varchar(100))
as
begin
declare @sql varchar(max);
declare @tabla varchar(max);
set @tabla = 'tabla_' + convert(varchar(10),@a) + '_' + convert(varchar(10),@b)
set @sql= '
IF OBJECT_ID('''+@tabla+''', ''U'') IS NULL
begin
CREATE TABLE dbo.' + @tabla + ' (datos varchar(100));
end
INSERT INTO dbo.' + @tabla + ' (datos) VALUES (''' + @datos + ''') ;
'
exec (@sql)
end
Básicamente lo que hacemos es generar una cadena con el batch a ejecutar y lo ejecutamos. El batch será distinto para cada combinación de valores @a y @b que se nos presente. A continuación vamos a ejecutar en un bucle llamadas a este procedimiento y mediremos los tiempos:
-- Test
declare @a int = 1
declare @b int
declare @exec int
while (@a<10)
begin
set @b=1
while (@b<10)
begin
set @exec=1
while (@exec < 100)
begin
exec pa_insert_dinámico @a,@b,'test'
set @exec=@exec+1;
end
set @b=@b+1;
end
set @a=@a+1;
end
La ejecución de este script en mi máquina es de unos 3.6 segundos y consumiremos aproximadamente 1.6 segundos de pura CPU.
Una posible alternativa a tener siempre la ejecución de código dinámico seria utilizar la técnica de cacheo que hemos comentado antes. Lo que haremos será comprobar si existe la tabla antes de crearla y si existe un procedimiento con el formato “pa_tabla_a_b”. Si no existe lo crearemos y las siguientes ejecuciones lo reutilizaremos.
create procedure pa_insert_dinámico_cache (@a int, @b int, @datos varchar(100))
as
begin
declare @tabla varchar(max);
declare @proc varchar(max)
declare @sql varchar(max);
set @tabla = 'tabla_' + convert(varchar(10),@a) + '_' + convert(varchar(10),@b)
set @proc= 'pa_' + @tabla;
IF OBJECT_ID(@tabla, 'U') IS NULL
begin
set @sql = 'CREATE TABLE dbo.' + @tabla + ' (datos varchar (100))';
exec (@sql);
end
IF OBJECT_ID('pa_' + @tabla, 'P') IS NULL
begin
set @sql = 'CREATE PROCEDURE dbo.' + @proc + ' (@datos varchar(100)) AS
INSERT INTO dbo.' + @tabla + ' (datos) VALUES (@datos)' ;
exec (@sql);
end
exec @proc @datos
end
Si ejecutamos el mismo test anterior en mi máquina pero llamando a este procedimiento con cacheo obtenemos los siguientes resultados:
Podemos ver que ya la primera ejecución es más rápida y que las posteriores, donde ya las tablas y procedimientos están creados, mejoran aún más este primer resultado.
El uso creativo de código dinámico para autogenerar código dinámico nos puede permitir tener lo mejor de ambos mundos. Obviamente este post es únicamente un ejemplo sencillo y no debe tomarse como algo listo para producción. Seguro que el ojo entrenado ha visto entre varias cosas problemáticas como:
* En el código de ejemplo existen concatenaciones poco seguras (riesgo de sql injection)
* No se está utilizando el nombre completo del procedimiento al hacer la ejecución (falta el esquema dbo)
* No existe invalidación de la caché en caso de cambio del código del procedimiento
En resumen, esta técnica bien empleada puede ayudarnos a mejorar el rendimiento de código que necesite ser dinámico. Mantengamos siempre la mente abierta ya que es posible que la necesidad de utilizar esta técnica venga por un mal diseño de la propia base de datos. Es decir, que la necesidad que nos lleva a usar distintos objetos de forma dinámica pudiera cubrirse, por ejemplo, con el uso de particionado.
