Optimizar Stored Procedures SP de SQL Server Veamos cómo optimizar el procedimiento almacenado con simples cambios en el código.
Incluir SET NOCOUNT ON: Con cada sentencia SELECT y DML, el servidor SQL devuelve un mensaje que indica el número de filas afectadas por esa declaración. Esta información es sobre todo útil para depurar el código,pero es inútil después de eso. Mediante el establecimiento de SET NOCOUNT ON, se puededesactivar la función de devolución de esta información adicional. Para los procedimientos almacenados que contienen varias declaraciones o contener Transact-SQL lazos, establecer SET NOCOUNT en ON puede proporcionar un aumento de rendimiento significativo, porque el tráfico de red se reduce considerablemente.
CREATE PROC dbo.ProcName AS SET NOCOUNT ON; --Procedure code here SELECT column1 FROM dbo.TblTable1 -- Reset SET NOCOUNT to OFF SET NOCOUNT OFF; GO
Use el nombre de esquema con el nombre del objeto: El nombre del objeto está calificado si se utiliza con el nombre de esquema. Nombre de esquemadebe ser utilizado con el nombre del procedimiento almacenado y con todos los objetos dereferencia dentro del procedimiento almacenado. Esta ayuda directa en encontrar el plan decumplimiento en lugar de buscar los objetos en el esquema de otras posibles antes de decidirse autilizar un plan almacenado en caché, si está disponible. Este proceso de búsqueda y decidir un esquema de un objeto conduce a compilar el procedimiento almacenado de bloqueo y disminuye el rendimiento del procedimiento almacenado. Por lo tanto, siempre se refieren los objetos connombre completo en el procedimiento almacenado como
SELECT * FROM dbo.MyTable -- Preferred method -- Instead of SELECT * FROM MyTable -- Avoid this method --And finally call the stored procedure with qualified name like: EXEC dbo.MyProc -- Preferred method --Instead of EXEC MyProc -- Avoid this method
No utilice el prefijo "sp_" en el nombre del procedimiento almacenado: Si un nombre de procedimiento almacenado comienza con "sp_", a continuación, busca el servidorSQL por primera vez en la base de datos maestra y luego en la base de datos actual período de sesiones. Búsqueda en la base de datos principal causa sobrecarga adicional e incluso un mal resultado si otro procedimiento almacenado con el mismo nombre se encuentra en la base de datos maestra.
Utilice el procedimiento almacenado sp_executesql en lugar de la instrucción EXECUTE: El procedimiento almacenado sp_executesql admite parámetros. Por lo tanto, utilizando el procedimiento almacenado sp_executesql en lugar de la instrucción EXECUTE mejorar lareutilización de código. El plan de ejecución de una instrucción dinámica puede ser reutilizado sólo si todos y cada personaje, incluido el caso, el espacio, los comentarios y los parámetros, es el mismo para dos estados. Por ejemplo, si ejecuta el lote a continuación:
DECLARE @Query VARCHAR(100) DECLARE @Age INT SET @Age = 25 SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' +CONVERT(VARCHAR(3),@Ag e) EXEC (@Query) Si volvemos a ejecutar el lote anterior con un valor diferente edad @, entonces el plan de ejecución de sentencia SELECT creado por @ Edad = 25 no se pueden reutilizar. Sin embargo, si escribimos el lote de arriba como se indica a continuación,
DECLARE @Query NVARCHAR(100) SET @Query = N'SELECT * FROM dbo.tblPerson WH ERE Age = @Age' EXECUTE sp_executesql @Query, N'@Age int', @Age = 25 el plan compilado de esta sentencia SELECT se puede reutilizar para diferentes valores del parámetro @ Edad. La reutilización del plan existente cumplimiento dará lugar a un mejor desempeño.
Trate de evitar el uso de cursores de SQL Server siempre que sea posible : El cursor utiliza una gran cantidad de recursos para la carga de procesamiento para mantener la posición del registro actual en un conjunto de registros y esto disminuye el rendimiento. Sitenemos que procesar los registros uno por uno en un bucle, entonces debemos utilizar la cláusula WHILE. Siempre que sea posible, se debe reemplazar el enfoque basado en cursor con SET enfoque. Debido a que el motor de SQL Server está diseñada y optimizada para llevar a cabobasado en conjuntos operación muy rápida. Una vez más, por favor, tenga en cuenta el cursor estambién una especie de bucle while.
Mantenga la transacción lo más corta posible: La duración de la transacción afecta el bloqueo y los interbloqueos. Bloqueo exclusivo no se libera hasta el final de la transacción. En el nivel superior de aislamiento, los bloqueos compartidostambién son mayores con la transacción. Por lo tanto, las transacciones largas medios segurosdurante más tiempo y cerraduras para las vueltas más tiempo en el bloqueo. En algunos casos, el bloqueo también se convierte en callejones sin salida. Por lo tanto, para una ejecución más rápida y menos bloqueo, la transacción debe ser lo más corto posible.
Utilice try-catch para el manejo de errores: Antes de códigos de SQL Server versión 2005 para el tratamiento de errores, hubo una gran porción de código en sí, porque una declaración de comprobación de errores fue escrito después de cada sentencia T-SQL. Más código siempre consume más recursos y tiempo. En SQL Server 2005, una manera sencilla se introduce una nueva con el mismo propósito. La sintaxis es la siguiente:
BEGIN TRY --Your t-sql code goes here END TRY BEGIN CATCH --Your error handling code goes here END CATCH
