Refactorización de código SQL – maneras de mejorar el desempeño de la base de datos (parte I) – Encapsular código como un procedimiento almacenado

El desempeño de la base de datos es un desafío para cada desarrollador y DBA. Incluso cuando algunas mejoras son hechas, siempre hay una pregunta – ¿hay algo más que se pueda hacer?

Puede haber algunas cosas. Algunas de las técnicas de mejoramiento de desempeño no están relacionadas con el código – una estrategia de indexación adecuada, añadir memoria, usar diferentes discos para archivos de datos, archivos de registros y copias de seguridad de bases de datos, usar discos más rápidos, optimizar el desempeño de tempdb.

Aparte de esto, hay algunos cambios que deben ser aplicados al código. Aunque cambiar el código suene como un proceso que consume mucho tiempo y es proclive a errores, no tiene que ser así.

Hay un par de técnicas que pueden ser usadas para envolver el código de mejor manera y por tanto mejorar el desempeño sin un análisis muy profundo o rescritura de código masiva. Reutilizar los planes de ejecución de SQL Server es uno de ellas.

Cuando una sentencia SQL es ejecutada, SQL Server alcanza el cache de procedimiento (la parte del grupo de memoria donde SQL Server almacena planes de ejecución) para el plan de ejecución para la sentencia SQL ejecutada. Si el plan de ejecución ya existe, SQL Server, en lugar de recompilar la sentencia SQL, la reutilizará y así ahorrará recursos. Si el plan de ejecución no existe, SQL Server lo creará y lo reusará después. Cada vez que un nuevo plan de ejecución es creado o el que existe es compilado, el desempeño de la consulta es afectado.

¿Qué puede ser hecho para reutilizar los planes de ejecución de SQL Server?

• Asegúrese de que todas las referencias de objetos está completas. Los algoritmos para emparejar nuevas sentencias SQL a planes de ejecución existentes requiere que todos los objetos estén completos.
• Use parámetros en lugar de valores constantes. Las siguiente sentencias no serán emparejadas como la misma en un plan de ejecución:
  

  SELECT production.product.productid 
  FROM   production.product 
  WHERE  productsubcategoryid = 1; 
  
  SELECT production.product.productid 
  FROM   production.product 
  WHERE  productsubcategoryid = 4;

  Así que se recomienda usar la siguiente:
  

  SELECT production.product.productid 
  FROM   production.product 
  WHERE  productsubcategoryid = @Parm

• Identificar las partes de las consultas que son más frecuentemente usadas o que toman más tiempo para correr y refactorizar el código SQL para encapsularlas como procedimientos almacenados o funciones. Esto propiciará la reutilización de planes de ejecución, ya que incluso un espacio extra puede causar que las consultas sean declaradas como diferentes y sean recompiladas.

¿Qué otros beneficios hay por usar procedimiento almacenados?

Refactorizando el código SQL para encapsular muchas sentencias en un solo procedimiento almacenado o función, la complejidad del código se reduce. El encapsular todo el código en procedimientos almacenados puede crear administración adicional que puede no ser necesaria, así que es mejor encapsular sólo las consultas más complejas y que duran mucho, y el código más frecuentemente usado. Los procedimientos almacenados no deberían contener lógica de decisión de bajo nivel y consultas simples.

De esta manera, el código específico es almacenado en un solo lugar, eliminando duplicados y reduciendo las líneas a mantener. Un procedimiento almacenado/función puede ser llamado desde muchas diferentes aplicaciones sin duplicar el código SQL. Aparte de mejorar el desempeño, los procedimientos almacenados tienen las siguientes ventajas sobre el código SQL:

• Seguridad – Conceder y revocar permisos en un procedimiento almacenado individual es simple y provee un acceso de control más granular. El acceso puede ser denegado a las tablas subyacentes y ser concedido sólo a través de procedimientos almacenados. Esto no puede ser hecho para consultas SQL parametrizadas.
• Usar parámetros de procedimiento limita los problemas de inyección SQL. El ingreso de parámetros no es tratado como código ejecutable, así que es más difícil insertar un comando en T-SQL dentro de los procedimientos y poner en peligro la base de datos.
• Tráfico de red servidor/cliente reducido – En un ambiente cliente/servidor, sólo la llamada para ejecutar el procedimiento es enviada a través de la red, a diferencia de mandar todas las líneas de código en el caso de ejecutar una consulta SQL.
• Mantenimiento más fácil – Dado que los procedimientos almacenados mantienen el código en un solo lugar, los cambios son aplicados sólo a un objeto, la base de datos subyacente. Un despliegue de arreglo de un procedimiento almacenado es simple e instantáneo, mientras que la aplicación en sí mismo se mantiene intacta.
• Reutilización de código – Las operaciones de la base de datos que son repetidas están todas encapsuladas en un procedimiento almacenado, así que no hay necesidad de rescribir el mismo código, eliminando la inconsistencia de código.
• Bloqueo de vendedor – Usar procedimientos almacenados permite bloquear el código si algún software está escrito por otras compañías.
• Versionado de código – Los scripts de creación de los procedimientos almacenados pueden ser añadidos a un sistema de control de versiones al igual que los scripts de creación para tablas, vistas y otros objetos de base de datos.

Una de las opciones para crear un procedimiento almacenado es usar el código SQL existente y manualmente crear un script de generación para el conjunto de sentencias que necesitan ser encapsuladas. Esto significa revisar las sentencias y:

1. Determinar todas las variables de entrada
2. Determinar todas las variables de salida
3. Escribir declaraciones para todas la variables usadas en el código
4. Envolverlo en BEGIN … END
5. Analizar el código para detectar errores SQL

Otra opción es hacer esto automáticamente usando ApexSQL Refactor.

ApexSQL Refactor es un complemento de SQL Server Management Studio y Visual Studio que crea procedimientos almacenados y funciones al refactorizar las consultas SQL existentes, sin la necesidad de escribir ningún código adicional o determinar y declarar parámetros manualmente. También expande comodines y completa nombres de objetos, lo cual también mejora el desempeño del código SQL.

1. Abra la consulta SQL que necesita ser encapsulada en SQL Server Management Studio o Visual Studio.
2. En el menú ApexSQL haga clic en ApexSQL Refactor

3. Seleccione la opción Encapsulate code as y luego Stored procedure, Scalar inline function, Table inline function o View, dependiendo de lo que hace el código:

   

4. En el diálogo Encapsulate code as, especifique el esquema y el nombre del procedimiento almacenado que será creado.
5. Haga clic en Generate preview para que ApexSQL Refactor liste todos los parámetros de entrada y salida usados en la consulta SQL y en el código del procedimiento almacenado mismo. ApexSQL Refactor automáticamente determina el tipo de dato del parámetro, ya sea que los parámetros son entradas o salidas, y genera el código de declaración.
6. Para crear el código que ejecuta el procedimiento almacenado recién creado con todos los parámetros necesarios, deje marcada la opción Modify source script to reference this new object.

7. Haga clic en el botón Open script para abrir el script generado en el editor de Consultas, de tal manera que pueda ser modificado.

   

8. 8. Haga clic en el botón Encapsulate
9. 9. Para crear un nuevo procedimiento almacenado en la base de datos a la actualmente está conectado, haga clic en el botón Yes.
   

   

   Si la opción Modify source script to reference this new object fue marcada, código de este tipo será generado después del script CREATE PROCEDURE:

-- Modified source:  

EXECUTE Customers.sp_UndoEncapsulated @comment, @errors OUTPUT, @sql, @print, @UndoLogId, @status, @n OUTPUT, @sql_err

Mejorar el desempeño de la base de datos SQL es un proceso complejo y que consume mucho tiempo. Aparte de las causas de hardware y otras no relacionadas con el código, el código en sí mismo puede bajar el desempeño. Sin embargo, esto no significa que el código tiene que ser rescrito desde cero. Use ApexSQL Refactor para “envolver” el código existente y crear procedimientos almacenados y funciones.

Este artículo es parte de una serie.

Ver todas las partes         Ver la siguiente parte

Traductor: Daniel Calbimonte