Cuando se trata de optimizar el rendimiento de las consultas SQL, la estructura de la cláusula WHERE juega un papel crucial. En esta publicación del blog, exploraremos el impacto en el rendimiento de utilizar funciones dentro de la cláusula WHERE en comparación con el uso de referencias directas a columnas. También discutiremos las implicaciones positivas de incorporar índices.

Preparando el escenario con datos y consultas de muestra

Para demostrar las variaciones en el rendimiento, consideremos una tabla hipotética SalesOrderHeader que contiene columnas como SalesOrderID, OrderDate, CustomerID y TotalDue. Utilizaremos consultas escritas para la base de datos de ejemplo AdventureWorks de SQL Server.

Echemos un vistazo a dos consultas que tienen como objetivo recuperar datos del año 2011 pero están estructuradas de manera diferente:

-- Consulta utilizando la función YEAR()
SELECT SalesOrderID, OrderDate, CustomerID, TotalDue
FROM SalesOrderHeader
WHERE YEAR(OrderDate) = 2011;

-- Consulta utilizando referencia directa a la columna
SELECT SalesOrderID, OrderDate, CustomerID, TotalDue 
FROM SalesOrderHeader
WHERE OrderDate BETWEEN '2011-01-01' AND '2011-12-31';

En la primera consulta, aplicamos la función YEAR() a la columna OrderDate dentro de la cláusula WHERE. Alternativamente, la segunda consulta se refiere directamente a la columna OrderDate, definiendo un rango de fechas en la cláusula WHERE.

Análisis del rendimiento sin índices

Cuando ejecutamos estas consultas sin un índice en la columna OrderDate, ambas consultas realizan un escaneo completo de la tabla para obtener los datos deseados. Esto significa que ambas consultas leen la misma cantidad de datos, lo que resulta en un rendimiento similar.

Tabla 'SalesOrderHeader'. Recuento de exploraciones 1, lecturas lógicas 689.

Sin un índice, el optimizador de SQL Server no puede realizar una operación de búsqueda eficiente para obtener directamente las filas coincidentes.

El poder de los índices – Cláusula WHERE

Ahora creemos un índice en la columna OrderDate:

CREATE NONCLUSTERED INDEX IX_OrderDate
ON Sales.SalesOrderHeader (OrderDate)
INCLUDE ([CustomerID],[TotalDue]);

Al volver a ejecutar nuestras dos consultas, la dinámica de rendimiento cambia notablemente. Con la función YEAR(), la primera consulta sigue utilizando un escaneo completo de la tabla:

Tabla 'SalesOrderHeader'. Recuento de exploraciones 1, lecturas lógicas 688.

Sin embargo, la consulta que utiliza la referencia directa a la columna utiliza estratégicamente el índice para una operación de búsqueda eficiente, lo que conduce a una reducción significativa en la E/S:

Tabla 'SalesOrderHeader'. Recuento de exploraciones 1, lecturas lógicas 119.

Desenmascarando la causa raíz

La diferencia considerable en el rendimiento se atribuye al uso de una función en la columna, lo que impide que el optimizador aproveche eficazmente el índice. Un índice mantiene los datos ordenados según el valor de la columna sin procesar. Sin embargo, aplicar una función como YEAR() o UPPER() transforma los valores, que no se almacenan en el índice, lo que dificulta la capacidad del optimizador para buscar directamente las entradas coincidentes.

Por el contrario, el filtrado de consultas basado en la referencia exacta a la columna puede aprovechar los valores de índice ordenados para localizar rápidamente las filas coincidentes, lo que resulta en un número significativamente menor de lecturas lógicas.

Conclusiones clave

Basándonos en este análisis, aquí hay algunas mejores prácticas para mejorar el rendimiento de SQL Server:

• Trate de evitar el uso de funciones en las cláusulas WHERE para mejorar el rendimiento. En lugar de envolver las columnas en funciones, haga referencia a ellas directamente.
• Si filtra por una columna transformada, cree un índice en la columna sin procesar.
• Siempre compare el rendimiento con y sin índices cuando se utilizan funciones en las cláusulas WHERE.

Recuerde, aprovechar los índices correctamente es vital para consultas SQL eficientes. Al comprender cómo las funciones interactúan con los índices, podemos optimizar las consultas para reducir las lecturas y mejorar los tiempos de respuesta.

Conclusión

La estructura de una consulta, principalmente si contiene funciones o referencias directas a columnas, afecta drásticamente su capacidad para utilizar índices para mejorar el rendimiento. Filtrar por una columna sin procesar generalmente permite búsquedas de índice, mientras que el uso de una función requiere escaneos completos. Al mantener las tareas fuera de las cláusulas WHERE y agregar índices de soporte, podemos mejorar significativamente el rendimiento de las consultas SQL.

No dude en comunicarse conmigo en Twitter si tiene alguna pregunta o comentario.