IF TYPE_ID (N'thetype') IS NOT NULL PRINT 'the type existe' ELSE PRINT 'No existe' GO
jueves, 22 de agosto de 2013
TIP SQL: Verificar La Existencia De Un User-defined Data Type/User-defined Table TYPE
Instrucción sencilla que verifica la existencia de un User-defined Data Type/User-defined Table TYPE.
La comparto porque esta no está disponible en los 'Templates' de Microsoft SQL Server Management Studio.
sábado, 15 de junio de 2013
Procedimientos Almacenados Multi-parametros: Stament Dinámico vs Stament Predefinido
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; GOCon 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)) GOPuede 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 GOEn 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
Etiquetas:
DATE,
sp_executesql,
SQL SERVER,
STORE PROCEDURE
Suscribirse a:
Entradas (Atom)