Consultas Dinámicas en Microsoft SQL Server
Introducción
Hoy hablare
de las consultas dinámicas, su funcionamiento y las prácticas que deben tenerse
para lograr un buen desempeño. Antes de iniciar, veremos la definición de una
consulta dinámica desde el punto de vista de Microsoft. Una consulta dinámica
es código SQL que es generado programáticamente (en parte o totalmente) por un programa
antes de que sea ejecutado. Esto es, durante la ejecución de un procedimiento
almacenado, se lleva a cabo la creación de un comando de consulta, de tal forma que, antes
de que termine la ejecución del procedimiento se ejecuta la consulta generada con las características
requeridas.
Desarrollo
Por ejemplo,
tenemos la siguiente consulta:
-- Declarar parametros
DECLARE @IdCategory nvarchar(7) = NULL
,@IdUnitMeasure smallint = 1
,@IdCurrency smallint = NULL
-- Establece consulta
SELECT Category
,Classification
,BrandName
,IdProduct
,[Description]
,PartNumber
,ISNULL(InventoryActual, 0) InventoryActual
,IsSerializable
FROM Catalog.VProduct
WHERE (IdCategory =
@IdCategory OR @IdCategory IS NULL)
AND (IdCurrency = @IdCurrency OR @IdCurrency
IS NULL)
AND (IdUnitMeasure = @IdUnitMeasure OR
@IdUnitMeasure IS NULL)
ORDER BY DisplayOrder,
Classification, BrandName, IDProduct
Es posible notar
que se ha generado una consulta que tiene 3 condiciones de filtro que pueden ser
indicados con nulos, de tal forma que en este caso las condiciones de
IdCategory y IdCurrency se han indicado con nulo y solo IdUnitMeasure se ha
especificado con valor, esta consulta puede ser generada dinámicamente para que
solo sea ejecutada la condición deseada, de la siguiente forma:
-- Declarar parametros
DECLARE @IdCategory nvarchar(7) = NULL
,@IdUnitMeasure smallint = 1
,@IdCurrency smallint = NULL
-- Declarar variables
DECLARE @SqlText nvarchar(1000)
DECLARE @cond int = 0
-- Genera Consulta Dinamica
SET @SqlText = N'SELECT Category
,Classification
,BrandName
,IdProduct
,[Description]
,PartNumber
,ISNULL(InventoryActual, 0) InventoryActual
,IsSerializable
FROM [Catalog].VProduct
WHERE ';
IF @IdCategory
IS NOT NULL
BEGIN
SET
@SqlText += N'
IdCategory = ''' + @IdCategory +'';
SET @cond
+=1;
END
IF @IdCurrency IS NOT NULL
BEGIN
IF @cond > 0 SET @SqlText += N' AND ';
SET
@SqlText += N'IdCurrency
= ' + CAST(@IdCurrency as nvarchar);
SET @cond += 1;
END
IF @IdUnitMeasure IS
NOT NULL
BEGIN
IF @cond > 0 SET @SqlText += N' AND ';
SET
@SqlText += N'IdUnitMeasure
= ' + CAST(@IdUnitMeasure as nvarchar);
END
SET @SqlText += N' ORDER BY DisplayOrder, Classification, BrandName,
IDProduct';
Este código
genera el comando siguiente para la consulta:
SELECT
Category
,Classification
,BrandName
,IdProduct
,[Description]
,PartNumber
,ISNULL(InventoryActual, 0) InventoryActual
,IsSerializable
FROM [Catalog].VProduct
WHERE IdUnitMeasure = 1 ORDER BY DisplayOrder,
Classification, BrandName, IDProduct
La
ejecución de esta consulta dinámica se lleva a cabo con el comando EXEC, como
se indica a continuación:
EXEC (@SqlText)
Hasta aquí,
la ejecución de la consulta dinámica se hace sencilla, sin embargo, esta
ejecución tiene un problema, no se guarda el plan de ejecución, ya que se ha
establecido con el uso del comando EXEC que se ejecute como única vez, además de
que la consulta no tiene parámetros, se han sustituido los valores de los parámetros
en la consulta. Ahora si queremos que la consulta se ejecute y se mantenga un
plan de ejecución será necesario hace unas pequeñas modificaciones al código
anterior, para quedar:
-- Declarar parametros
DECLARE @IdCategory nvarchar(7) = NULL
,@IdUnitMeasure smallint = 1
,@IdCurrency smallint = NULL
-- Declarar variables
DECLARE @SqlText nvarchar(1000)
DECLARE @cond int = 0
-- Genera Consulta Dinamica
SET @SqlText = N'SELECT Category
,Classification
,BrandName
,IdProduct
,[Description]
,PartNumber
,ISNULL(InventoryActual, 0) InventoryActual
,IsSerializable
FROM [Catalog].VProduct
WHERE ';
IF @IdCategory
IS NOT NULL
BEGIN
SET
@SqlText += N'
IdCategory = @IdCat ';
SET @cond
+=1;
END
IF @IdCurrency IS NOT NULL
BEGIN
IF @cond > 0 SET @SqlText += N' AND ';
SET @SqlText += N'IdCurrency = @IdCur ';
SET @cond += 1;
END
IF @IdUnitMeasure IS
NOT NULL
BEGIN
IF
@cond > 0 SET
@SqlText += N' AND ';
SET
@SqlText += N'IdUnitMeasure
= @IdUnit ';
END
SET @SqlText += N' ORDER BY DisplayOrder, Classification, BrandName,
IDProduct';
-- Ejecuta consulta dinamica
EXEC sp_executesql @statement =
@SqlText,
@params
= N'@IdCat nvarchar(7),
@IdUnit smallint, @IdCur smallint',
@IdCat = @IdCategory,
@IdUnit = @IdUnitMeasure,
@IdCur = @IdCurrency
Se observa
que se ha incluido una llamada a un procedimiento almacenado del sistema que se
denomina sp_executesql, este procedimiento ejecuta una instrucción de Transact-SQL
o un lote de instrucciones que puede reutilizarse muchas veces, o uno que se ha
generado dinámicamente, como es el caso anterior. La instrucción o lote de
Transact-SQL puede contener parámetros incorporados, como se ha indicado en el
ejemplo.
En la
declaración del procedimiento almacenado se pueden identificar tres componentes
principales de parámetros, el que se identifica como @statement que es el que
se asocia con la consulta dinámica, @params que debe contener todos los parámetros
que puedes ser utilizados en la consulta dinámica y por último cada uno de los parámetros
indicados con su valor correspondiente. En el caso de nuestro ejemplo se tienen
tres posibles parámetros y se declaran 3 posibles valores.
Es
importante mencionar que, la ejecución de la consulta dinámica utilizando el procedimiento
almacenado del sistema, permite mantener el plan de ejecución de la consulta dinámica
en memoria, ya que al poder manejar parámetros, estos permiten la reutilización
de la consulta, solo cambiará el valor del parámetro, de esta forma, para el
ejemplo que tenemos, es posible que se mantengan 8 planes de ejecución,
dependiendo de la presencia de los parámetros, con lo cual se hace mas
eficiente el uso de la consulta dinámica.
Existe la
posibilidad de que algún desarrollo requiera el uso de múltiples consultas dinámicas,
lo que generara una gran cantidad de espacio para mantener los planes de
ejecución de las mismas, es por ello que en estos casos conviene validar el
valor de la opción de configuración denominada “optimize for ad hoc workloads”, está opción se usa para mejorar la
eficacia de la memoria caché del plan para cargas de trabajo que contienen
muchos lotes ad hoc de un solo uso. Cuando esta opción se establece en 1, el
Motor de base de datos almacena un pequeño apéndice del plan compilado en la
memoria caché de planes de ejecución cuando se compila un lote por primera vez,
en lugar del plan completo compilado. Esto ayuda a aliviar la presión de la
memoria al no permitir que la memoria caché del plan de ejecución se llene con
planes compilados que no se reutilizan.
Conclusión
Las
consultas dinámicas son muy útiles cuando se desean manejar consultas que requiere
la presencia de varios parámetros, que pueden contener valores o bien la
ausencia de ellos, en cualquier caso, la consulta debe obtener información. En
nuestro caso se trata de una consulta que se utiliza para filtrar la
presentación de la información.