TIPS PARA OPTIMIZAR STORED PROCEDURE

• Incluir la declaración SET NOCOUNT ON : Con cada instrucción 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 útil sobre todo en la depuración del código, pero es inútil después de eso. Al establecer SET NOCOUNT ON, podemos desactivar la función de devolución de esta información adicional. Para los procedimientos almacenados que contengan varias declaraciones o contener bucles de Transact-SQL, el establecimiento de SET NOCOUNT en ON puede proporcionar un aumento de rendimiento importante porque el tráfico de red se reduce considerablemente.

CREATE PROC dbo.ProcNameAS
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. El nombre de esquema se debe utilizar con el nombre del procedimiento almacenado y con todos los objetos de referencia dentro del procedimiento almacenado. Esta ayuda directamente a encontrar el plan compilado en lugar de buscar los objetos de otros esquemas posibles antes de decidirse a utilizar un plan en caché, si está disponible. Este proceso de búsqueda y decidir un esquema de un objeto lleva a un bloqueo de compilación en el procedimiento almacenado y disminuye el rendimiento del procedimiento almacenado. Por lo tanto, siempre se refieren los objetos con nombre completo en el procedimiento almacenado:

 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, SQL Server busca primero en la base de datos master y luego en la base de datos actual en donde están las sesiones con las que estamos trabajando. Buscar en la base de datos master causas sobrecarga adicional e incluso un mal resultado si otro procedimiento almacenado con el mismo nombre se encuentra en la base de datos master.
• Use IF EXISTS (SELECT 1) en lugar de (SELECT *): Para comprobar la existencia de un registro de otra tabla, se utiliza la cláusula IF EXISTS. El SI devuelve cláusula EXISTS verdadera si el valor devuelto por una instrucción interna, ya sea un único valor "1 "o todas las columnas de un registro o registros completos. La salida de la declaración de la residencia no se utiliza. Por lo tanto, para reducir al mínimo los datos para el procesamiento y la transferencia de la red, debemos utilizar "1" en la cláusula SELECT de una instrucción interna, como se muestra a continuación:
  IF EXISTS (SELECT 1 FROM sysobjects
          WHERE name = 'MyTable' AND type = 'U')
• Utilice el procedimiento almacenado sp_executesql en lugar de la instrucción EXECUTE.El procedimiento almacenado sp_executesql admite parámetros. Así, mediante el procedimiento almacenado sp_executesql en lugar de la instrucción EXECUTE mejorar la aptitud para la reutilización del código. El plan de ejecución de una sentencia dinámica se pueden reutilizar sólo si todas y todos los personajes, incluido el caso, el espacio, los comentarios y los parámetros, es el mismo para dos estados. Por ejemplo, si ejecutamos el siguiente lote:DECLARE @Query VARCHAR(100)DECLARE @Age INTSET @Age = 25SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)EXEC (@Query) Si volvemos a ejecutar el lote anterior con valor diferente Edad @, entonces el plan de ejecución de sentencia SELECT creado para @ Edad = 25 no se pueden reutilizar. Sin embargo, si escribimos el lote de arriba se indican a continuación:DECLARE @Query NVARCHAR(100)SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'EXECUTE sp_executesql @Query, N'@Age int', @Age = 25el plan compilado de esta instrucción SELECT se reutilizarán para el valor de diferentes parámetros de 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 gastos de proceso para mantener la posición del registro actual en un conjunto de registros y esto reduce el rendimiento. Si necesitamos a los registros de proceso de uno por uno en un bucle, entonces debemos utilizar la cláusula WHILE. Siempre que sea posible, se debe sustituir el enfoque basado en el cursor con el enfoque basado en conjuntos. Debido a que el motor de SQL Server está diseñado y optimizado para realizar la operación SET-base muy rápido. Una vez más, por favor, tenga en cuenta el cursor es también una especie de bucle while.
• Mantenga la transacción lo más pequeño posible: La longitud de la transacción afecta el bloqueo y los interbloqueos. El bloqueo exclusivo no se libera hasta el final de la transacción. En el nivel de aislamiento más alto, los bloqueos compartidos también son mayores con la transacción. Por lo tanto, las transacciones largas medios bloqueos por más tiempo y bloqueos durante más tiempo se convierte en el bloqueo. En algunos casos, el bloqueo también se convierte en callejones sin salida. Así, para una ejecución más rápida y menos de bloqueo, la transacción debe ser lo más corto posible.
• Utilice try-catch para el control de errores: Antes de la versión de SQL 2005 para el control de errores, se usaba mucho código, porque una declaración de verificación de error fue escrito después de cada instrucción T-SQL. Más código siempre consume más recursos y tiempo. En SQL Server 2005, una manera simple introducción de la nueva con el mismo fin. La sintaxis es la siguiente: BEGIN TRY--Your t-sql code goes hereEND TRYBEGIN CATCH--Your error handling code goes hereEND CATCH
  
  .Ayuda: Optimizar Store Procedure