Rendimiento

Como DBAs, lo más normal va a ser encontrarnos con consultas que, aunque devuelven el resultado esperado, no están optimizadas y pueden llevar a problemas de rendimiento muy serios. Estos patrones de diseño ineficaces son conocidos como «Query Anti-Patterns» y por muy bien diseñada y optimizada que esté tu base de datos van a dilapidar su rendimiento. Vamos a explorar algunos de los Anti-Patterns más comunes y cómo evitarlos para asegurarnos de que nuestras consultas SQL sean lo más eficientes posible.

Anti-Patterns Comunes y Cómo Evitarlos

La optimización de consultas SQL es una mezcla de arte y ciencia. La habilidad para identificar y corregir anti-patterns en nuestras consultas puede marcar la diferencia entre una base de datos que funciona sin problemas y una que causa dolores de cabeza constantes. Aunque algunos anti-patterns pueden parecer inofensivos a primera vista, su impacto acumulativo, cuando se repiten mucho o se solapan puede ser devastador. En este artículo, vamos a ver varios de estos anti-patterns con ejemplos concretos y soluciones prácticas.

El más común: Select *

Uno de los anti-patterns más comunes y dañinos es el uso de SELECT *. Al seleccionar todas las columnas de una tabla, no solo estamos recuperando datos innecesarios, sino que también podemos estar comprometiendo la seguridad y el rendimiento de nuestra consulta. Este problema se agrava si estamos ante una tabla de un modelo tabular optimizada con índices columnares pues perderemos su gran potencial propio de su diseño, la capacidad de leer solo las columnas necesarias.

Ejemplo:

SELECT * 
FROM Employees 
WHERE Department = 'IT';

Solución:

Especificar sólo las columnas necesarias, de esta manera, reducimos la cantidad de datos transferidos y hacemos que nuestra consulta sea más clara y manejable. Además podremos aprovecharnos de índices que cubran completamente nuestras consultas.

SELECT EmployeeID, FirstName, LastName
FROM Employees 
WHERE Department = 'IT';

Funciones en las Columnas

Usar funciones en las columnas dentro de las cláusulas WHERE es otro anti-pattern que puede degradar significativamente el rendimiento. Las funciones en las columnas evitan que SQL Server use índices, resultando en escaneos de tabla completos. Es lo que se conoce como problemas de sargabilidad de los índices, y consiste en que, al usar un filtro de búsqueda que no se puede aplicar a todos los niveles de la estructura B-tree (árbol invertido), el motor de base de datos tiene que recorrer completamente el nivel hoja buscando coincidencias.

Ejemplo:

SELECT * 
FROM Orders 
WHERE YEAR(OrderDate) = 2023;

Solución:

Reescribir la consulta para evitar la función en la columna. Con esta modificación, permitimos que SQL Server utilice índices sobre OrderDate, mejorando notablemente el rendimiento.

SELECT * FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01';

Subconsultas Correlacionadas en el WHERE

Las subconsultas correlacionadas dentro de una cláusula WHERE pueden ser extremadamente costosas, ya que la subconsulta se ejecuta una vez por cada fila en la tabla externa.

Ejemplo:

SELECT * 
FROM Employees e 
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE Department = e.Department);

Solución:

Utilizar un JOIN o CROSS APPLY para evitar la subconsulta correlacionada. Aunque puede haber excepciones en función del tamaño de las tablas, esta aproximación es generalmente más eficiente, ya que la subconsulta se ejecuta una sola vez y los resultados se unen a la tabla principal reduciendo drásticamente el número de lecturas en disco.

SELECT e.* 
FROM Employees e
JOIN (
    SELECT Department, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY Department
) d ON e.Department = d.Department
WHERE e.Salary > d.AvgSalary;

Subconsultas en el SELECT

Las subconsultas en la cláusula SELECT pueden causar problemas similares a las subconsultas en WHERE, ya que se ejecutan por cada fila de la tabla principal.

Ejemplo:

SELECT 
    EmployeeID,
    (SELECT DepartmentName FROM Departments WHERE DepartmentID = Employees.DepartmentID) AS DepartmentName
FROM Employees;

Solución:

Usar un JOIN para incluir la información necesaria. De esta forma, la subconsulta se elimina y la consulta puede beneficiarse de una menor cantidad de lecturas.

SELECT 
    e.EmployeeID, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

UNION en Lugar de UNION ALL

El uso de UNION en lugar de UNION ALL puede resultar en un rendimiento deficiente, ya que UNION elimina duplicados, lo cual requiere una operación adicional de ordenación y comparación para lo que es necesario cargar todos los datos en memoria. Es común encontrarse con consultas con UNION por pereza y no escribir los 4 carácteres extra pero, si no es estrictamente necesario nunca es una buena idea.

Ejemplo:

SELECT EmployeeID FROM CurrentEmployees
UNION
SELECT EmployeeID FROM FormerEmployees;

Solución:

Si estamos seguros de que no hay duplicados, usar UNION ALL. De esta manera, evitamos el trabajo adicional de eliminar duplicados y mejoramos la eficiencia de la consulta.

SELECT EmployeeID FROM CurrentEmployees
UNION ALL
SELECT EmployeeID FROM FormerEmployees;

Conversiones Implícitas

Las conversiones implícitas ocurren cuando SQL Server necesita convertir los tipos de datos de una columna o variable para que coincidan. Esto puede tener un impacto negativo en el rendimiento, especialmente cuando involucra columnas indexadas, ya que puede evitar que los índices se utilicen de manera eficiente.

Ejemplo:

SELECT * FROM Orders WHERE OrderID = ‘12345’;

En este ejemplo, si OrderID es un entero y estamos comparándolo con una cadena, SQL Server tendrá que convertir OrderID a una cadena para realizar la comparación, lo que puede evitar el uso de índices.

Solución:

Asegurarse de que los tipos de datos coincidan. De esta manera, evitamos la conversión implícita y permitimos que SQL Server utilice los índices de manera eficiente.

SELECT * FROM Orders WHERE OrderID = 12345;

Localizando Anti-Patterns con X-Events (ojo, esto ya es muy friki)

Los eventos extendidos son una herramienta muy poderosa en nuestra búsqueda de problemas. Gracias a su capacidad de capturar, en tiempo real, consultas que cumplan unos requisitos establecidos y, a una novedad introducida en SQL Server 2022 como es el evento query_antipattern, nos van a permitir localizar estas consultas mal diseñadas de una manera sencilla. O eso dice la teoría. Os dejo por aquí el script que he usado yo para generar esta sesión de x-events:

USE [master]
GO 
IF EXISTS (SELECT * FROM sys.dm_xe_sessions WHERE NAME = 'query_antipattern') 
      DROP event session [query_antipattern] ON server
GO 
CREATE EVENT SESSION [query_antipattern] ON SERVER  
     ADD EVENT sqlserver.query_antipattern (   
        ACTION(sqlserver.client_app_name,sqlserver.plan_handle, 
               sqlserver.query_hash,sqlserver.query_plan_hash,
               sqlserver.sql_text)
          ) ADD TARGET package0.ring_buffer(SET max_memory=(500)) 
GO
ALTER EVENT SESSION query_antipattern ON SERVER STATE = START
GO

Probando los Anti-Patterns X-Events

Ahora que ya hemos visto la teoría vamos a volver al mundo real. Podemos localizar los anti-patterns detectados por este evento extendido si miramos en la DMV sys.dm_xe_map_values que registra los distintos tipos de eventos.

Como ves, hay 5 antipatrones, el quinto es el único que coincide con alguno de los que hemos visto nosotros en este artículo. Sobre el resto no hay más documentación, Microsoft ha decidido sacar esta novedad en X-Events pero no lo ha documentado. 

En mis pruebas, he conseguido generar una consulta que active el primero de los tipos de antipatrón, el LargeIn, pero os voy a ser sinceros, no ha sido fácil. Para lograrlo he escrito una consulta con un filtro where CustomerID IN y a continuación le he pasado 2500 parámetros separados por comas. Con menos parámetros no he conseguido hacer saltar la alerta. El antipatrón de conversiones que impiden el Seek sí que es relativamente sencillo verlo y salta ante cualquier consulta con un error de conversión. 

Sobre el resto no os puedo decir más, llevo más de una semana con este artículo escrito, buscando información y haciendo pruebas pero no lo he conseguido ver. En algún sitio he visto que han conseguido el fallo por un antipatrón LargeNumberOfOrInPredicate con las pruebas que a mi me han dado el LargeIn pero yo no lo he podido reproducir. Igual que ellos no pudieron reproducir el LargeIn. Sobre el resto de tipos de Anti-Patterns no he conseguido más información. No sé qué significa Max en este contexto. En el caso de NonOptimalOrLogic he llegado a pensar que es un problema con las lógicas OR pero no he conseguido reproducirlo ni con 21.500 OR en una misma consulta. Es más, ni con 21.500 OR en una misma consulta mostró ninguno de los otros Anti-Patterns.

Conclusión

Identificar, resolver y evitar estos anti-patterns en nuestras consultas T-SQL puede suponer una mejora significativa en el rendimiento de nuestras bases de datos. Es crucial siempre revisar y optimizar las consultas en ejecución, especialmente en sistemas críticos donde el rendimiento es esencial.Al aplicar buenas prácticas y evitar estos anti-patterns, no solo mejoramos la eficiencia de nuestras consultas, sino que también contribuimos a la estabilidad a largo plazo de nuestras aplicaciones. 

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!