SQL Server Consultas Dinámicas

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.