martes, 28 de mayo de 2013

SQL SERVER: OPTIMIZACIÓN Y BUENAS PRACTICAS

IMPLEMENTE “SET NOCOUNT ON” EN SUS CONSULTAS

Al ejecutar  sentencias tipo DML en un procedimiento almacenado, el servidor SQL regresa un mensaje indicando el número de filas afectadas por el proceso. Aunque esta información puede ser útil para depurar el código, resulta obsoleta si no se está depurando. Al establecer el uso de  SET NOCOUNT ON, se desactiva la función de retorno de esta información adicional. En procedimientos almacenados formados por varias instrucciones o declaraciones de sentencias DML, habilitar SET NOCOUNT ON puede ayudarnos a conseguir un aumento de rendimiento considerable, además, si tomamos en cuenta una base de datos que contenga decenas de Procedimientos Almacenados para uso en una aplicación orientada a múltiples clientes al mismo tiempo, un detalle tan sencillo como este puede aportar una diferencia notable. Se podría usar a modo de ejemplo el siguiente esqueleto:

CREATE PROC dbo.StoreProcedureExample
(
@variable_1 INT,
@variable_2 VARCHAR(10),
@variable_n BIT
)
AS
BEGIN
      SET NOCOUNT ON;
            --INSTRUCCIÓN 1
            --INSTRUCCIÓN 2
            --INSTRUCCIÓN N
      SET NOCOUNT OFF;
END

EVITE USAR SELECT *

Aunque resulte fácil y cómodo usar el comodín (*) para traer todos los campos, este debe omitirse y en su lugar especificarse los campos que sean necesario traerse. El uso del comodín impide además un uso efectivo de forma eficiente de los índices.  En caso de que sean todos como el uso del “*” establece, especifique cada uno de ellos, un simple ALT+F1 sobre el nombre de la tabla seleccionada traerá su estructura, por lo que copiar, pegar los campos y añadir una coma supone un esfuerzo mínimo.
Me he topado en varias ocasiones que al hacer un chequeo de existencia se recurre por lo regular a la siguiente sintaxis:

IF EXISTS (SELECT * FROM  Tabla WHERE Campo=Condicionante)   
      BEGIN   
            --INSTRUCCIONES 1,2…N    
      END

Evítelo, no existe ningún motivo para cargar más trabajo a la base de datos trayendo todos los campos si lo único que se desea saber es si existe. Puede sustituirse sin ningún problema el * por ‘’ o 1 cumpliendo con su función de chequeo de existencia sin necesidad de traer datos:

IF EXISTS (SELECT '' FROM  Tabla WHERE Campo=Condicionante)   
      BEGIN   
            --INSTRUCCIONES 1,2…N    
      END

O bien:

IF EXISTS (SELECT 1 FROM  Tabla WHERE Campo=Condicionante)   
      BEGIN   
            --INSTRUCCIONES 1,2…N    
      END

Ambos le darán la misma funcionalidad sin necesidad de cargar de trabajo adicional e innecesario a la base de datos.

USE NOLOCK… SABIAMENTE

El uso de NOLOCK puede mejorar considerablemente la velocidad de algunas consultas. Al usar NOLOCK en las consultas se establece que la lectura no “atrapa” la tabla y esta puede ser leída al mismo tiempo por todos los usuarios. Sin embargo, hay que tomar en cuenta que el conjunto de datos mostrados  es considerado como una “lecturasucia”. Esto significa que los datos mostrados podrían no ser del todo precisos, pudiendo estos encontrarse en medio de alguna transacción del tipo DELETE, UPDATE o INSERT. Aun así, para el caso de tablas estáticas como en algunos casos las dedicadas a catálogos fijos y para escenarios de reportes cuya información es más que nada  histórica y que por lo tanto no se verá afectada en el momento, podría suponer una mejora aceptable sobre todo si son consultas frecuentes en un ambiente multiusuario.
No se puede usar NOLOCK de forma indiscriminada en todas los Procedimientos Almacenados, es más una cuestión de evaluación sobre escenarios particulares.
NOLOCK se usa al momento de efectuar una consulta y solo aplica a las tablas. Su estructura seria la siguiente:

SET NOCOUNT ON
      SELECT
      Campo1,
      Campo2,
      .
      .
      CampoN
      FROM Tabla1  T1(NOLOCK)
      INNER JOIN Tabla2 T2(NOLOCK) ON T2.CampoX=T1.CampoX
      INNER JOIN Tabla3 T3(NOLOCK) ON T3.CampoY=T2.CampoY 
      WHERE
      <Condicionantes>
SET NOCOUNT OFF

EVITE UTILIZAR EL PREFIJO “SP_” EN EL NOMBRE DEL LOS PROCEDIMIENTOS ALMACENADOS

Utilizar el prefijo “sp_” para etiquetar procedimientos almacenados, aunque “intuitivo”, puede resultar en una nefasta idea; ¿Por qué?…  SQL Server reconoce el prefijo “sp_” como “System Stored Procedure”, es decir, un procedimiento almacenado de Sistema.

Este detalle en particular apoya en la forma que SQL Server usa para localizar el procedimiento almacenado cuando intentamos ejecutarlo, asumiendo que se trata de un procedimiento almacenado de sistema, y por lo tanto deberá estar en la base de datos MASTER, donde se ubican todos los Procedimientos Almacenados de esta clase. Intentando primeramente localizar el procedimiento en la base de datos MASTER. Al no encontrarlo continua su búsqueda la base de datos activa, provocando con esto una caída del rendimiento que, aunque parezca insignificante en un ambiente de transacciones pequeño podría influir significantemente si estamos hablando de un ambiente mucho más grande en donde se ejecuten miles de transacciones por minuto.

UTILICE SP_EXECUTESQL EN LUGAR EXECUTE

Se recomienda el uso de sp_executesql , en lugar de la instrucción EXECUTE o EXEC al ejecutar código dinámico. Esto debido a que sp_executesql admite la sustitución de parámetros, es mucho más versátil que EXECUTE y además genera planes de ejecución;  Lo que aumenta las probabilidades de que al volverlo a utilizar resulte más eficaz.

EVADA EL USO DE CURSORES

Los cursores son una herramienta usada para acceder y modificar el resultado de una cláusula SELECT fila por fila. El problema con su uso es que consumen una enorme cantidad de recursos, especialmente de memoria. Siempre que sea posible, se debe omitir el uso de cursores o minimizar su implementación. Algunas alternativas y sustitutos al uso de cursores pueden ser:




UTILICE ADECUADAMENTE LAS VARIABLES TIPO TABLA & TABLAS TEMPORALES

¿Siempre es mejor usar variables tipo tabla en lugar de tablas temporales?, revisemos las características y diferencias entre una y otra.

Variables tipo tabla:
§  
  • Su uso en procedimientos almacenados provoca menos re compilaciones.
  • Apuntan a estructuras de memoria por lo que producen menos consumo de recursos que las tablas temporales.
  • Su contenido no siempre está en memoria. En caso de que se inserte una cantidad grande de registros esta se almacena en TEMPDB.
  • El contenido de la variable no es afectado por el comando ROLLBACK.
  • No se pueden generar al vuelo.
  • No usan paralelismo (multiple threads) en su plan de ejecución. Su uso para tablas de concurrencia alta o con una gran cantidad de datos puede afectar su desempeño, siendo este menor en comparación con una tabla temporal.
  • No se les puede agregar índices.
  • No se les pueden modificar ni truncar una vez creadas.


Tablas temporales:

  • Se almacenan automáticamente en la base de datos TEMPDB.
  • Su creación puede ocasionar bloqueos en las tablas sysobjects, sysindexes y afectar a todo el servidor.
  • Permite el uso de índices.
  • Su uso en procedimientos almacenados puede provocar una re compilación continua.
  • A grandes rasgos, pueden tratarse como una tabla normal.


En general, se puede sugerir el uso de variables tipo tabla siempre que sea posible en lugar de tablas temporales. Use estas últimas solo en caso de que se maneje una cantidad muy grande de información y siempre procurando crear su estructura previamente nunca creándolas “al vuelo”.

IMPLEMENTACION DE SQL DINAMICO (O  CODIGO ROJO)

Aunque en general el uso de SQL Dinámico esta algo condenado debido a que una mala implementación puede resultar en un grieta de seguridad que de entrada a un severo caso de SQL Injection. La implementación que se sugiere esta encapsulada dentro de un procedimiento almacenado, no es del todo dinámica para la aplicación cliente, es decir, no le permite estructurar sentencias libres y está orientada más que nada a procedimientos almacenados cuya función principal es una consulta parametrizada con opciones variables. Usare la base de datos AdventureWorksDW2008R2 para ilustrar un ejemplo. Probaremos con el siguiente procedimiento almacenado:

CREATE PROCEDURE BeforeRedCodeSELECT   
(   
@ProductKey INT=NULL,   
@SalesOrderLineNumber TINYINT=NULL,   
@OrderQuantity SMALLINT=NULL,
@CurrencyKey INT=NULL    
)   
AS   
BEGIN   
SET NOCOUNT ON   
   
 SELECT    
 FIS.ProductKey,   
 FIS.OrderQuantity,   
 FIS.UnitPrice,  
 FIS.SalesOrderNumber,
 FIS.CurrencyKey,
 FIS.SalesOrderLineNumber
 FROM FactInternetSales FIS(NOLOCK)   
 WHERE FIS.ProductKey=ISNULL(@ProductKey, FIS.ProductKey)   
 AND FIS.SalesOrderLineNumber= ISNULL (@SalesOrderLineNumber, FIS.SalesOrderLineNumber)   
 AND FIS.OrderQuantity= ISNULL (@OrderQuantity, FIS.OrderQuantity)  
 AND FIS.CurrencyKey= ISNULL (@CurrencyKey, FIS.CurrencyKey) 
    
SET NOCOUNT OFF   
END 

En este  caso la consulta acepta valores nulos y se usa ISNULL (se puede usar COALESCE en su lugar, pero para razones prácticas en el ejemplo no tiene mucho caso y ISNULL es un poco más rápido que COALESCE) para dar la flexibilidad al procedimiento almacenado y poder consultar ya sea usando uno, los cuatro o cualquier combinación de parámetros. Ahora, convirtiendo la consulta en SQL Dinámico, quedaría de la siguiente forma:
CREATE PROCEDURE AfterRedCodeSELECT 
( 
@ProductKey INT=NULL,     
@SalesOrderLineNumber TINYINT=NULL,     
@OrderQuantity SMALLINT=NULL, 
@CurrencyKey INT=NULL     
) 
AS 
BEGIN 
SET NOCOUNT ON 
  
 DECLARE @Query AS NVARCHAR(MAX) 
         
 SET @Query=N'    
 SELECT      
 FIS.ProductKey,     
 FIS.OrderQuantity,     
 FIS.UnitPrice,    
 FIS.SalesOrderNumber, 
 FIS.CurrencyKey, 
 FIS.SalesOrderLineNumber  
 FROM FactInternetSales FIS(NOLOCK)     
 WHERE 1=1 ' 
  
 IF @ProductKey IS NOT NULL 
  BEGIN 
   SET @Query = @Query + ' AND FIS.ProductKey = @ProductKey ' 
  END 
   
 IF @SalesOrderLineNumber IS NOT NULL 
  BEGIN 
   SET @Query = @Query + ' AND FIS.SalesOrderNumber = @SalesOrderLineNumber ' 
  END 
  
 IF @OrderQuantity IS NOT NULL 
  BEGIN 
   SET @Query = @Query + ' AND FIS.OrderQuantity = @OrderQuantity ' 
  END 
   
 IF @CurrencyKey IS NOT NULL 
  BEGIN 
   SET @Query = @Query + ' AND FIS.CurrencyKey =@CurrencyKey ' 
  END 
   
 --PRINT @Query 
  
  EXECUTE sp_executesql  @Query 
 , N'@ProductKey INT, @SalesOrderLineNumber TINYINT, @OrderQuantity SMALLINT, @CurrencyKey INT ' 
 , @ProductKey = @ProductKey                 
 , @SalesOrderLineNumber = @SalesOrderLineNumber 
 , @OrderQuantity = @OrderQuantity 
 , @CurrencyKey = @CurrencyKey 
   
SET NOCOUNT OFF 
END 


Aquí el procedimiento almacenado solo incluirá las condiciones que sean necesarias y que no tengan un valor nulo. Ambos procedimientos devuelven los mismos resultados y son equivalentes el uno al otro. Usando SQL Profiler, podemos apreciar la diferencia en la optimización de recursos:


Las lecturas se reducen para este escenario particular, en poco más de un 80%, la duración en un 60% y en el uso del CPU, un cambio total.

Por lo que se aprecia el SQL Dinámico podría no será tan malo como parece y puede ofrecernos alternativas creativas para optimizar nuestras consultas. Evalué sus consultas complejas y pesadas para sopesar si una refactorización usando SQL Dinámico puede ayudarle, cuidando siempre que esta no se preste para casos de SQL Injection.

APRENDA A APLICAR  INDICES DE FORMA OPTIMIZADA

Es ya bien conocido el uso de índices para acelerar consultas en SQL Server y otros motores de bases de datos. Estos le indican al motor de la base de datos en turno, que esa columna o conjunto de columnas se van  a usar con mayor frecuencia, lo cual hace que los datos se almacenen en memoria para contar con un acceso más eficiente a los datos.
Es aconsejable que los índices se formen sobre los campos que encuentren frecuentemente en alguno de los siguientes escenarios:
  • Son PRIMARY KEY o FOREIGN KEY.
  • Se usan frecuentemente para enlazar tablas con JOIN.
  • Se usan de forma habitual dentro de los procedimientos almacenados o en consultas emparejados con alguno de los siguientes comandos: BETWEEN, ORDER BY, GROUP BY, TOP, DISTINCT (Aunque ya mencionamos que estos deben de evitarse en lo posible, si no hay otra alternativa, hay que considerar sus campos para la aplicación de índices).
  • Son de uso corriente para filtrados en la clausura WHERE.


Pese a que los índices generen una mejora en tiempos de respuesta, tampoco se puede abusar indiscriminadamente de ellos. En contraposición a su mejora de tiempo de respuesta cada índice ocupa un espacio equivalente al número de registros en la tabla, penalizando el rendimiento en la base de datos al ejecutar  INSERT, UPDATES y DELETE.
Omita crear índices con campos que contienen pocos valores, como aquellos que son de tipo BIT o CHAR (de uno a cuatro) y asegúrese de no agregar el mismo índice con distintos nombres.
El estudio profundo de la creación, diseño y mantenimiento de índices escapa del alcance de este articulo (espero escribir algo más elaborado sobre el tema de índices en un futuro), sin embargo es importante que se tome el tiempo para comprenderlos. Su correcta aplicación puede favorecer por mucho el tiempo de respuesta de sus consultas.

RECOMENDACIONES ADICIONALES

  • Si el procedimiento almacenado contiene demasiadas sentencias IF-ELSE para ejecutar distintos procesos acorde a ciertos parámetros, resulta más eficiente separar cada bloque y encapsularlos en procedimientos almacenados diferentes; Esto para evitar que el plan de ejecución cambie acorde al valor del resultado de la expresión IF, desperdiciando con esto la ventaja de el plan de ejecución en memoria y la pre compilación.
  • Evite el uso de la instrucción SELECT-INTO. Al ejecutarse se bloquean las tablas involucradas. Aplique en su lugar la sentencia INSERT INTO-SELECT.
  • Trate de usar siempre la instrucción JOIN antes que cualquier sub consulta.
  • Use la instrucción BETWEEN en lugar de IN siempre que sea posible.
  • En el caso de que se use la instrucción LIKE, evite el uso del comodín “%” al inicio de la cadena a buscar. Esto debido a que si se aplica, la búsqueda tendría que leer todos los datos de la tabla o tablas involucradas para responder a la consulta. Se recomienda que existan al menos tres caracteres antes del comodín.
  • Evite en la medida de lo posible el uso de DISTINCT
  • En caso de usar la instrucción UNION y existiera la seguridad de que en los SELECT involucrados no se obtendrán registros duplicados, entonces lo recomendable en este escenario es sustituir UNION por UNION ALL para evitar que se haga uso implícito de la instrucción DISTINCT, ya que esta aumenta el consumo de recursos.
  • Evada siempre que sea posible el uso de ORDER BY. Al igual que DISTINCT consume una elevada cantidad de recursos. Considere si es realmente necesario usarlo o, si por otro lado se puede dejar el ordenamiento de los resultados a la aplicación que recibirá los datos.
  • Use SQL Profiler y levante una traza para estudiar el rendimiento de los procedimientos almacenados de mayor concurrencia y vea opciones para refactorizar el proceso. Otorgue especial atención a las columnas CPU, Duraction, reads y writes para optimizar el uso de memoria y el I/O generado. 
  • Mantenga las transacciones lo más cortas posibles dentro de un procedimiento almacenado. Esto favorece a la reducción del número de bloqueos, promoviendo a acelerar el rendimiento general de la base de datos.
  • Utilice el Plan de Ejecución para revisar sus consultas, entienda como SQL Server está ejecutando sus consultas  y evalué sus sugerencias.
  • Si requiere almacenar volúmenes de texto muy grandes, pero son menores a 8000 caracteres, use el tipo de dato VARCHAR en lugar de TEXT.
  • Evalué cuidadosamente el uso de CHAR y VARCHAR dependiendo si el campo en el que se va a usar varía mucho o no de tamaño. Esto para sopesar rendimiento de velocidad sobre rendimiento de almacenamiento. SQL Server procesa más rápido las columnas de longitud fija. Use CHAR para columnas de poca variación en longitud y VARCHAR para aquellas que no tienen una longitud estable o promedio.
  • No use columnas con tipos de datos FLOAT, REAL o DATETIME como FOREIGN KEY.


RESUMEN


Los puntos presentados en este artículo sirven como esquema general para la optimización de consultas y procedimientos almacenados, pero, cabe aclarar que la optimización es más bien un asunto de entornos particulares. Quizá lo ideal sería establecer estas recomendaciones desde el inicio de un desarrollo, si el desarrollo ya está en producción lo más coherente es evaluar qué puntos pueden o no aplicarse. Menciono esto último por escenarios en los que, por ejemplo, ya se efectuó todo el desarrollo de “X” aplicación; Algunos programadores usan el retorno del conteo de las filas afectadas para “validar” que el procedimiento almacenado efectivamente se ejecutó y en base a eso la aplicación sigue o no adelante (esto no debería ser así, pero pasa… con mayor frecuencia de la que uno espera). En este escenario, aplicar SET NOCOUNT ON podría generar muchos errores, en especial si la aplicación es grande y compleja y la cantidad de procedimientos almacenados a los que se les debe aplicar también es alta. El uso indiscriminado de NOLOCK en las tablas puede crear también problemas, sobre todo si la aplicación debe mostrar información consistente y se aplica esta práctica a procedimientos que deben de respetar ciertas transacciones antes de efectuar cambios. En conclusión, la optimización es un trabajo más que nada artesanal y depende en muchos casos de en qué punto (inicio, mitad o fase final del desarrollo) decidamos aplicarla.



Dedico un agradecimiento especial a Faustino Quintero, DBA que me mostro las curiosidades del SQL Dinámico para aumentar el performance.

REFERENCIAS:


http://www.devtroce.com

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.