Aquellos que en nuestro desempeño como programadores de aplicaciones enlazadas a bases de datos nos enfrentamos al reto de la optimización de nuestros 'staments'. Todos en el algún momento hemos tenido la necesidad realizar consultas con parametros múltiples que puedan ser usados individualmente o combinados.
¿A que me refiero? Tomando como referencia la tabla Employee de la base de datos AdventureWorks supongamos que debemos crear un procedimiento almacenado que obtenga la información general de los empleados en base a los parámetros cargo (JobTitle), género (Gender) y fecha de contratación (HireDate), el procedimiento debe ser capaz de devolver un conjunto de resultados usando cada uno de los parámetros por separados o cada una de las combinaciones posibles para los parámetros a los que se proporcionó un valor válido o ninguno de ellos.
La primera vez que se me presentó un escenario similar creé un procedimiento que crea la sentencia en linea evualuando los valores de los parámetros y concatenandolo a la sentencia base si el valor era válido. Este es un ejemplo:
USE AdventureWorks
GO
CREATE PROCEDURE uspObtenerEmpleadosv1
@JobTitle NVARCHAR(50),
@Gender NCHAR(1),
@HireDate1 DATE,
@HireDate2 DATE
AS
DECLARE @Statement NVARCHAR(MAX), @Where NVARCHAR(500)
SET @Where = ''
SET @Statement = N'SELECT e.BusinessEntityID, e.NationalIDNumber, e.LoginID,
e.OrganizationNode, e.OrganizationLevel, e.JobTitle,
e.BirthDate, e.MaritalStatus, e.Gender,
e.HireDate, e.SalariedFlag, e.VacationHours,
e.SickLeaveHours, e.CurrentFlag, e.rowguid, e.ModifiedDate
FROM
HumanResources.Employee e'
IF @JobTitle IS NOT NULL
SELECT @Where = @Where + CASE @Where WHEN '' THEN ' WHERE e.JobTitle LIKE ''' + @JobTitle + ''''
ELSE ' AND WHERE e.JobTitle LIKE ''' + @JobTitle + '''' END
IF @Gender IS NOT NULL
SELECT @Where = @Where + CASE @Where WHEN '' THEN ' WHERE e.Gender = ''' + @Gender + ''''
ELSE ' AND e.Gender = ''' + @Gender + '''' END
IF @HireDate1 IS NOT NULL AND @HireDate2 IS NOT NULL
SELECT @Where = @Where + CASE @Where WHEN '' THEN ' WHERE e.HireDate BETWEEN ''' + CONVERT(VARCHAR(10),@HireDate1,111) + ''' AND ''' + CONVERT(VARCHAR(10),@HireDate2,111) + ''''
ELSE ' AND (e.HireDate BETWEEN ''' + CONVERT(VARCHAR(10),@HireDate1,111) + ''' AND ''' + CONVERT(VARCHAR(10),@HireDate2,111) + ''')' END
ELSE
IF @HireDate1 IS NOT NULL AND @HireDate2 IS NULL
SELECT @Where = @Where + CASE @Where WHEN '' THEN ' WHERE e.HireDate = ''' + CONVERT(VARCHAR(10),@HireDate1,111) + ''''
ELSE ' AND e.HireDate = ''' + CONVERT(VARCHAR(10),@HireDate1,111) + '''' END
SET @Statement = @Statement + @Where;
EXECUTE sp_executesql @Statement;
GO
Con el paso del tiempo y conforme la base de datos creció me di cuenta que si bien este método cumplía la función para la que lo había creado el tiempo de respuesta era muy alto. Fue entonces cuando, gracias a la indicación de un compañero, pude darme cuenta que el problema radicaba en que por cada ejecución del procedimiento almacenado SQL Server tenía que compilar la consulta nuevamente, esto es innecesario y contraproducente a menos que el diseño de la(s) tabla(s) hayan sufrido cambios significativos. La idea de crear un procedimiento almacenado es, corrijanme si me equivoco, que al momento de la creación del mismo el motor de base de datos trace un plan de consulta(ejecución) reutilizable que disminuya el tiempo de respuesta; el método anterior tira por el suelo esa utilidad.
Para remediar la situación mi compañero me planteó la alternativa que hoy comparto con ustedes:
USE AdventureWorks
GO
CREATE PROCEDURE uspObtenerEmpleadosv2
@JobTitle NVARCHAR(50) = NULL,
@Gender NCHAR(1) = NULL,
@HireDate1 DATE = NULL,
@HireDate2 DATE = NULL
AS
SELECT
e.BusinessEntityID, e.NationalIDNumber, e.LoginID,
e.OrganizationNode, e.OrganizationLevel, e.JobTitle,
e.BirthDate, e.MaritalStatus, e.Gender,
e.HireDate, e.SalariedFlag, e.VacationHours,
e.SickLeaveHours, e.CurrentFlag, e.rowguid, e.ModifiedDate
FROM
HumanResources.Employee e
WHERE ((@JobTitle IS NULL) OR (e.JobTitle LIKE @JobTitle))
AND ((@Gender IS NULL) OR (e.Gender = @Gender))
AND ((@HireDate1 IS NULL AND @HireDate2 IS NULL)
OR ((@HireDate1 IS NOT NULL AND @HireDate2 IS NOT NULL) AND (e.HireDate BETWEEN @HireDate1 AND @HireDate2))
OR (@HireDate1 IS NOT NULL AND e.HireDate = @HireDate1))
GO
Puede apreciarse que ahora la sentencia es única y predeterminada, por lo tanto el plan de ejecución trazado por el motor de base de datos durante la creación del procedimiento será utilizado tal cual y no deberá redefinirse.
Añado dos sentencias de ejecución para que comparen el 'Plan de Ejecución Estimado' de una y otra versión
EXEC uspObtenerEmpleadosv1 @Title = N'Sales Representative' --NVARCHAR(50)
,@Gender = N'F' --NCHAR(1)
,@HireDate1 = '2003-01-01' --'YYYY-MM-DD' DATE
,@HireDate2 = '2005-12-31' --'YYYY-MM-DD' DATE
GO
EXEC uspObtenerEmpleadosv2 @Title = N'Sales Representative' --NVARCHAR(50)
,@Gender = N'F' --NCHAR(1)
,@HireDate1 = '2003-01-01' --'YYYY-MM-DD' DATE
,@HireDate2 = '2005-12-31' --'YYYY-MM-DD' DATE
GO
En conclusión, un paso adelante para mejorar el tiempo de respuesta de nuestros procedimientos almacenados es eliminar la construcción de sentencias t-sql en ellos.
Agradezco sus comentarios constructivos.
Saludos
