Optimizar Stored Procedures SP de SQL Server

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 puede desactivar 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 esquema debe ser utilizado con el nombre del procedimiento almacenado y con todos los objetos de referencia dentro del procedimiento almacenado. Esta ayuda directa en encontrar el plan de cumplimiento en lugar de buscar los objetos en el esquema de otras posibles antes de decidirse a utilizar 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 con nombre 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 servidor SQL 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.

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. La cláusula IF EXISTS devuelve verdadero si se devuelve el valor de una declaración interna, ya sea un único valor "1" o todas las columnas de un registro o registros completos. La salida del comando interno no se utiliza. Por lo tanto, para reducir al mínimo los datos para el procesamiento y transferencia de la red, debemos utilizar "1" en la cláusula SELECT de una declaració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. Por lo tanto, utilizando el procedimiento almacenado sp_executesql en lugar de la instrucción EXECUTE mejorar la reutilizació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),@Age)
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 WHERE 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. Si tenemos 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 cabo basado en conjuntos operación muy rápida. 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 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 compartidos también son mayores con la transacción. Por lo tanto, las transacciones largas medios seguros durante 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


tags: procedimiento sql, procedimiento en sql, procedimientos sql, sql procedimientos, procedimiento almacenado en sql, procedures en sql, procedimiento en sql server, sql servers, texto sql, sql server store procedure, store procedures sql server, sql

En esta sección encontrarás una mezcla de códigos recopilados de fuentes públicas de Internet y otros creados por ASP TEAM. Compartimos recursos útiles de buena fe para formar una base de conocimiento en el desarrollo de aplicaciones en ASP Clásico.