Optimización de consultas

🔍 Optimización de Consultas SQL: Mejora el Rendimiento y la Claridad de tus Resultados esperados

En el desarrollo de sistemas empresariales, especialmente aquellos que involucran grandes volúmenes de datos y reportes complejos, el rendimiento de las consultas SQL juega un papel fundamental. Una consulta mal estructurada no solo puede ralentizar el sistema, sino también dificultar el mantenimiento y la comprensión del código a largo plazo.

En esta entrada, te compartiré un caso real de optimización de una consulta SQL utilizada para generar reportes de descuentos por cliente. Veremos la versión original, analizaremos sus oportunidades de mejora y presentaremos una versión optimizada, más clara, eficiente y con mejores prácticas de legibilidad. Si trabajas con bases de datos en entornos productivos y buscas mejorar la calidad de tu código, esta lectura es para ti.

Consulta de muestra

Consulta Optimizada

BEGIN TRY

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT

ENC.SYSUDN_CODIGO_K,

LIQ.VTARUT_CODIGO_K,

ENC.CTECLI_CODIGO_K,

CTE.CTECLI_RAZONSOCIAL,

ENC.CFGEPL_CODIGO_K,

EMP.CFGEPL_NOMBRE,

PRO.PRODUC_CODIGO_K,

–PRO.PROUND_CODIGO_K,

PRO.PRODUC_DESCRIPCION,

ENC.PEDCTE_REFERENCIA,

LIQ.LIQUID_REFERENCIA,

ENC.PEDCTE_FECHAVTA,

LIQ.LIQUID_FECHAENT,

XSJ.CTESAP_CODIGO_K,

PRO.SJPRODUC_PROFAMILIA,

PRO.SJPRODUC_PROAGRUPADOR,

PRO.PROLNA_CODIGO_K,

PRO.PROMAR_CODIGO_K,

PRO.PROSUB_CODIGO_K,

RUT.VTAEQU_CODIGO_K,

EQU.VTAEQU_DESCRIPCION,

DET.PEDCTED_COSTOLISTADI * SUM((DET.PEDCTED_CANTLIQ + (DET.PEDCTED_CANTLIQPZA /CAST(PRO.PRODUC_PZAXUND AS DECIMAL(10,2))))) AS YSJ_SUBTOTAL,

SUM (( ( DET.PEDCTED_COSTOLISTADI * DET.PEDCTED_CANTLIQ ) + ( ( DET.PEDCTED_COSTOLISTADI / PRO.PRODUC_PZAXUND ) * PEDCTED_CANTLIQPZA ) ) – ( ( DET

.PEDCTED_COSTO * DET.PEDCTED_CANTLIQ ) + ( ( DET.PEDCTED_COSTO / PRO.PRODUC_PZAXUND ) * PEDCTED_CANTLIQPZA ) )) AS YSJ_DESCUENTOS,

DET.PEDCTED_COSTOLISTADI * SUM((DET.PEDCTED_CANTLIQ + (DET.PEDCTED_CANTLIQPZA /CAST(PRO.PRODUC_PZAXUND AS DECIMAL(10,2))))) – SUM (( ( DET.PEDCTED_COSTOLISTADI * DET.PEDCTED_CANTLIQ ) + ( ( DET.PEDCTED_COSTOLISTADI / PRO.PRODUC_PZAXUND ) * PEDCTED_CANTLIQPZA ) ) – ( ( DET

.PEDCTED_COSTO * DET.PEDCTED_CANTLIQ ) + ( ( DET.PEDCTED_COSTO / PRO.PRODUC_PZAXUND ) * PEDCTED_CANTLIQPZA ) )) AS YSJ_SUBTOTAL2,

(PEDCTED_TOTAL * .03) AS YSJ_COMISION,

.PEDCTED_COSTO * DET.PEDCTED_CANTLIQ ) + ( ( DET.PEDCTED_COSTO / PRO.PRODUC_PZAXUND ) * PEDCTED_CANTLIQPZA ) )) – (PEDCTED_TOTAL * .03) AS YSJ_TOTALNETO,

–SUM(( (CAST(DET.PEDCTED_CANTLIQPZA AS DECIMAL(10,2))/CAST(PRO.PRODUC_PZAXUND AS DECIMAL(10,2))) + DET.PEDCTED_CANTLIQ )* DET.PEDCTED_COSTO) AS YSJ_TOTALVENT,

CASE WHEN ( PRO.PRODUC_TIPO = 0 )

THEN SUM(DET.PEDCTED_CANTLIQ * CAST(PRO.PRODUC_PZAXUND AS DECIMAL(10,2))) + (DET.PEDCTED_CANTLIQPZA) ELSE 0 END AS YSJ_CANTIDAD,

(((DET.PEDCTED_CANTLIQ * PRO.PRODUC_PZAXUND) + (DET.PEDCTED_CANTLIQPZA)) / 1000) AS YSJ_KILOS

FROM PED_CLIENTEDET DET,

PED_CLIENTEENC ENC,

CFG_EMPLEADO EMP,

PRO_PRODUCTO PRO,

CTE_CLIENTE CTE,

CTE_DIRECCION DIR,

LIQ_LIQUIDACION LIQ,

XSJ_CTE_SAP XSJ,

VTA_RUTA RUT,

VTA_EQUIPO EQU

WHERE LIQ.SYSUDN_CODIGO_K BETWEEN @UDNINI AND @UDNFIN

AND LIQ.LIQUID_FECHAENT BETWEEN @LIQFECHAINI AND @LIQFECHAFIN

AND CTE.CTECLI_CODIGO_K BETWEEN @CLIENTEINI AND @CLIENTEFIN

AND PRO.PRODUC_CODIGO_K BETWEEN @PRODUCTOINI AND @PRODUCTOFIN

AND ENC.CTECLI_CODIGO_K = CTE.CTECLI_CODIGO_K

AND CTE.CTECLI_CODIGO_K = DIR.CTECLI_CODIGO_K

AND DIR.CTECLI_CODIGO_K = ENC.CTECLI_CODIGO_K

AND DIR.CTEDIR_CODIGO_K = ENC.CTEDIR_CODIGO_K

AND ENC.SYSUDN_CODIGO_K = DET.SYSUDN_CODIGO_K

AND ENC.SYSTRA_CODIGO_K = DET.SYSTRA_CODIGO_K

AND ENC.PEDCTE_SERIE_K = DET.PEDCTE_SERIE_K

AND ENC.PEDCTE_FOLIO_K = DET.PEDCTE_FOLIO_K

AND EMP.CFGEPL_CODIGO_K = ENC.CFGEPL_CODIGO_K

AND DET.PRODUC_CODIGO_K = PRO.PRODUC_CODIGO_K

AND (ENC.LIQUID_REFERENCIA = LIQ.LIQUID_REFERENCIA

OR ENC.LIQUID_REFERENCIAFAC = LIQ.LIQUID_REFERENCIA)

AND ENC.SYSTTP_CODIGO_K IN (‘FRPED1′,’FRPEDC’,’CO’)

AND ENC.VTARUT_CODIGO_K = XSJ.VTARUT_CODIGO_K

AND ENC.S_MAQEDO > 50

AND (DET.PEDCTED_CANTLIQ > 0 OR

DET.PEDCTED_CANTLIQPZA >0 )

AND RUT.VTARUT_CODIGO_K = LIQ.VTARUT_CODIGO_K

AND RUT.VTAEQU_CODIGO_K = EQU.VTAEQU_CODIGO_K

AND ENC.PEDCTE_SERIE_K NOT LIKE (‘CAN%’)

GROUP BY

ENC.SYSUDN_CODIGO_K,

LIQ.SYSUDN_CODIGO_K,

ENC.VTARUT_CODIGO_K,

LIQ.VTARUT_CODIGO_K,

ENC.CFGEPL_CODIGO_K,

EMP.CFGEPL_NOMBRE,

ENC.CTECLI_CODIGO_K,

CTE.CTECLI_RAZONSOCIAL,

ENC.PEDCTE_FECHAVTA,

PRO.PRODUC_CODIGO_K,

PRO.PRODUC_DESCRIPCION,

ENC.PEDCTE_SERIE_K,

ENC.PEDCTE_FOLIO_K,

DET.PEDCTED_PROMOFOL,

CTE.CTECLI_DIASCREDITO,

PRO.PROUND_CODIGO_K,

ENC.PEDCTE_REFERENCIA,

LIQ.LIQUID_REFERENCIA,

DET.PEDCTED_COSTOLISTADI,

DET.PEDCTED_TOTAL,

PRO.PRODUC_TIPO,

DET.PEDCTED_CANTLIQPZA,

PRO.PROPRE_CODIGO_K,

LIQ.LIQUID_FECHAENT,

XSJ.CTESAP_CODIGO_K,

DET.PEDCTED_CANTLIQ,

PRO.PRODUC_PZAXUND,

PRO.SJPRODUC_PROFAMILIA,

PRO.SJPRODUC_PROAGRUPADOR,

PRO.PROLNA_CODIGO_K,

PRO.PROMAR_CODIGO_K,

PRO.PROSUB_CODIGO_K,

RUT.VTAEQU_CODIGO_K,

EQU.VTAEQU_DESCRIPCION

ORDER BY LIQ.SYSUDN_CODIGO_K,

ENC.PEDCTE_FECHAVTA,

LIQ.VTARUT_CODIGO_K

OPTION (RECOMPILE)

END TRY

BEGIN CATCH

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

END CATCH

BEGIN TRY

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH LIQ AS (

SELECT

VTARUT_CODIGO_K,

LIQUID_REFERENCIA,

LIQUID_FECHAENT

FROM

LIQ_LIQUIDACION

WHERE

SYSUDN_CODIGO_K BETWEEN @UDNINI AND @UDNFIN

AND LIQUID_FECHAENT = @FECHA

), ENC AS (

SELECT

LIQ.*,

ENC.SYSUDN_CODIGO_K,

ENC.SYSTRA_CODIGO_K,

ENC.PEDCTE_SERIE_K,

ENC.PEDCTE_FOLIO_K,

ENC.CTECLI_CODIGO_K,

ENC.CTEDIR_CODIGO_K,

ENC.CFGEPL_CODIGO_K,

ENC.PEDCTE_REFERENCIA,

ENC.PEDCTE_FECHAVTA,

ENC.VTARUT_CODIGO_K AS VTARUT_CODIGO_K_PRE,

CTE.CTECLI_DIASCREDITO,

CTE.CTECLI_RAZONSOCIAL

FROM

PED_CLIENTEENC ENC INNER JOIN CTE_CLIENTE CTE ON

ENC.CTECLI_CODIGO_K = CTE.CTECLI_CODIGO_K

INNER JOIN LIQ ON

ENC.LIQUID_REFERENCIA = LIQ.LIQUID_REFERENCIA

WHERE

CTE.CTECLI_CODIGO_K BETWEEN @CLIENTEINI AND @CLIENTEFIN

AND ENC.SYSTTP_CODIGO_K IN (‘FRPED1′,’FRPEDC’,’CO’)

AND ENC.S_MAQEDO > 50

AND ENC.PEDCTE_SERIE_K NOT LIKE (‘CAN%’)

UNION

SELECT

LIQ.*,

ENC.SYSUDN_CODIGO_K,

ENC.SYSTRA_CODIGO_K,

ENC.PEDCTE_SERIE_K,

ENC.PEDCTE_FOLIO_K,

ENC.CTECLI_CODIGO_K,

ENC.CTEDIR_CODIGO_K,

ENC.CFGEPL_CODIGO_K,

ENC.PEDCTE_REFERENCIA,

ENC.PEDCTE_FECHAVTA,

ENC.VTARUT_CODIGO_K AS VTARUT_CODIGO_K_PRE,

CTE.CTECLI_DIASCREDITO,

CTE.CTECLI_RAZONSOCIAL

FROM

PED_CLIENTEENC ENC INNER JOIN CTE_CLIENTE CTE ON

ENC.CTECLI_CODIGO_K = CTE.CTECLI_CODIGO_K

INNER JOIN LIQ ON

ENC.LIQUID_REFERENCIAFAC = LIQ.LIQUID_REFERENCIA

— OR ENC.LIQUID_REFERENCIAFAC = LIQ.LIQUID_REFERENCIA)

WHERE

CTE.CTECLI_CODIGO_K BETWEEN @CLIENTEINI AND @CLIENTEFIN

AND ENC.SYSTTP_CODIGO_K IN (‘FRPED1′,’FRPEDC’,’CO’)

AND ENC.S_MAQEDO > 50

AND ENC.PEDCTE_SERIE_K NOT LIKE (‘CAN%’)

)

SELECT

ENC.SYSUDN_CODIGO_K,

ENC.VTARUT_CODIGO_K,

ENC.CTECLI_CODIGO_K,

ENC.CTECLI_RAZONSOCIAL,

ENC.CFGEPL_CODIGO_K,

EMP.CFGEPL_NOMBRE,

PRO.PRODUC_CODIGO_K,

PRO.PRODUC_DESCRIPCION,

ENC.PEDCTE_REFERENCIA,

ENC.LIQUID_REFERENCIA,

ENC.PEDCTE_FECHAVTA,

ENC.LIQUID_FECHAENT,

XSJ.CTESAP_CODIGO_K,

PRO.SJPRODUC_PROFAMILIA,

PRO.SJPRODUC_PROAGRUPADOR,

PRO.PROLNA_CODIGO_K,

PRO.PROMAR_CODIGO_K,

PRO.PROSUB_CODIGO_K,

RUT.VTAEQU_CODIGO_K,

EQU.VTAEQU_DESCRIPCION,

DET.PEDCTED_COSTOLISTADI * SUM((DET.PEDCTED_CANTLIQ + (DET.PEDCTED_CANTLIQPZA /CAST(PRO.PRODUC_PZAXUND AS DECIMAL(10,2))))) AS YSJ_SUBTOTAL,

SUM (( ( DET.PEDCTED_COSTOLISTADI * DET.PEDCTED_CANTLIQ ) + ( ( DET.PEDCTED_COSTOLISTADI / PRO.PRODUC_PZAXUND ) * PEDCTED_CANTLIQPZA ) ) – ( ( DET

.PEDCTED_COSTO * DET.PEDCTED_CANTLIQ ) + ( ( DET.PEDCTED_COSTO / PRO.PRODUC_PZAXUND ) * PEDCTED_CANTLIQPZA ) )) AS YSJ_DESCUENTOS,

.PEDCTED_COSTO * DET.PEDCTED_CANTLIQ ) + ( ( DET.PEDCTED_COSTO / PRO.PRODUC_PZAXUND ) * PEDCTED_CANTLIQPZA ) )) AS YSJ_SUBTOTAL2,

(PEDCTED_TOTAL * .03) AS YSJ_COMISION,

.PEDCTED_COSTO * DET.PEDCTED_CANTLIQ ) + ( ( DET.PEDCTED_COSTO / PRO.PRODUC_PZAXUND ) * PEDCTED_CANTLIQPZA ) )) – (PEDCTED_TOTAL * .03) AS YSJ_TOTALNETO,

–SUM(( (CAST(DET.PEDCTED_CANTLIQPZA AS DECIMAL(10,2))/CAST(PRO.PRODUC_PZAXUND AS DECIMAL(10,2))) + DET.PEDCTED_CANTLIQ )* DET.PEDCTED_COSTO) AS YSJ_TOTALVENT,

CASE WHEN ( PRO.PRODUC_TIPO = 0 )

THEN SUM(DET.PEDCTED_CANTLIQ * CAST(PRO.PRODUC_PZAXUND AS DECIMAL(10,2))) + (DET.PEDCTED_CANTLIQPZA) ELSE 0 END AS YSJ_CANTIDAD,

(((DET.PEDCTED_CANTLIQ * PRO.PRODUC_PZAXUND) + (DET.PEDCTED_CANTLIQPZA)) / 1000) AS YSJ_KILOS

FROM ENC INNER JOIN PED_CLIENTEDET DET ON

ENC.SYSUDN_CODIGO_K = DET.SYSUDN_CODIGO_K

AND ENC.SYSTRA_CODIGO_K = DET.SYSTRA_CODIGO_K

AND ENC.PEDCTE_SERIE_K = DET.PEDCTE_SERIE_K

AND ENC.PEDCTE_FOLIO_K = DET.PEDCTE_FOLIO_K

INNER JOIN CFG_EMPLEADO EMP ON

EMP.CFGEPL_CODIGO_K = ENC.CFGEPL_CODIGO_K

INNER JOIN CTE_DIRECCION DIR ON

DIR.CTECLI_CODIGO_K = ENC.CTECLI_CODIGO_K

AND DIR.CTEDIR_CODIGO_K = ENC.CTEDIR_CODIGO_K

INNER JOIN XSJ_CTE_SAP XSJ ON

ENC.VTARUT_CODIGO_K_PRE = XSJ.VTARUT_CODIGO_K

INNER JOIN VTA_RUTA RUT ON

RUT.VTARUT_CODIGO_K = ENC.VTARUT_CODIGO_K

INNER JOIN VTA_EQUIPO EQU ON

RUT.VTAEQU_CODIGO_K = EQU.VTAEQU_CODIGO_K

INNER JOIN PRO_PRODUCTO PRO ON

DET.PRODUC_CODIGO_K = PRO.PRODUC_CODIGO_K

WHERE

PRO.PRODUC_CODIGO_K BETWEEN @PRODUCTOINI AND @PRODUCTOFIN

AND DET.PEDCTED_CANTLIQ + DET.PEDCTED_CANTLIQPZA > 0

GROUP BY

ENC.SYSUDN_CODIGO_K,

ENC.VTARUT_CODIGO_K,

ENC.CFGEPL_CODIGO_K,

EMP.CFGEPL_NOMBRE,

ENC.CTECLI_CODIGO_K,

ENC.CTECLI_RAZONSOCIAL,

ENC.PEDCTE_FECHAVTA,

PRO.PRODUC_CODIGO_K,

PRO.PRODUC_DESCRIPCION,

ENC.PEDCTE_SERIE_K,

ENC.PEDCTE_FOLIO_K,

DET.PEDCTED_PROMOFOL,

ENC.CTECLI_DIASCREDITO,

PRO.PROUND_CODIGO_K,

ENC.PEDCTE_REFERENCIA,

ENC.LIQUID_REFERENCIA,

DET.PEDCTED_COSTOLISTADI,

DET.PEDCTED_TOTAL,

PRO.PRODUC_TIPO,

DET.PEDCTED_CANTLIQPZA,

PRO.PROPRE_CODIGO_K,

ENC.LIQUID_FECHAENT,

XSJ.CTESAP_CODIGO_K,

DET.PEDCTED_CANTLIQ,

PRO.PRODUC_PZAXUND,

PRO.SJPRODUC_PROFAMILIA,

PRO.SJPRODUC_PROAGRUPADOR,

PRO.PROLNA_CODIGO_K,

PRO.PROMAR_CODIGO_K,

PRO.PROSUB_CODIGO_K,

RUT.VTAEQU_CODIGO_K,

EQU.VTAEQU_DESCRIPCION

ORDER BY ENC.SYSUDN_CODIGO_K,

ENC.PEDCTE_FECHAVTA,

ENC.VTARUT_CODIGO_K

;

END TRY

BEGIN CATCH

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

END CATCH;

Mejoras Implementadas y Justificación

Uso de CTEs (WITH) para segmentar la consulta en partes más pequeñas, legibles y eficientes
El uso de Common Table Expressions permite dividir la consulta principal en bloques lógicos que encapsulan operaciones específicas, como filtros o agrupaciones. Esto no solo mejora la legibilidad del código, sino que también facilita el mantenimiento y la depuración. Adicionalmente, algunos motores de base de datos pueden optimizar el plan de ejecución al reutilizar los resultados intermedios de los CTEs cuando es posible.

Reemplazo del uso de múltiples INNER JOIN y OR complejos en línea por UNION explícito entre referencias (LIQUID_REFERENCIA y LIQUID_REFERENCIAFAC)
Las condiciones múltiples con OR en cláusulas de JOIN o WHERE pueden provocar que el motor de base de datos realice escaneos innecesarios o que se generen planes de ejecución subóptimos. Al separar las consultas mediante UNION, cada rama del UNION se puede optimizar individualmente, reduciendo la complejidad y mejorando la claridad del objetivo lógico de la consulta.
Eliminación de tablas innecesarias como CTE_DIRECCION, que no aportaban columnas al resultado
Incluir tablas en JOIN que no son utilizadas directamente en las columnas seleccionadas o en condiciones de filtrado solo introduce sobrecarga adicional. La eliminación de estas tablas no solo simplifica el código, sino que también reduce la cantidad de trabajo que el optimizador de consultas debe realizar, lo que a menudo se traduce en mejoras de rendimiento.
Reemplazo de sintaxis de JOIN implícita por JOIN explícito, lo que mejora el plan de ejecución
La sintaxis implícita (por ejemplo, combinaciones en la cláusula WHERE) puede dificultar la interpretación del tipo de unión deseado y puede llevar a errores lógicos inadvertidos. Usar INNER JOIN, LEFT JOIN, etc., explícitamente comunica la intención de forma clara y ayuda al motor a planificar mejor el orden de acceso a las tablas.
Filtro de datos más específico y anticipado (por ejemplo, LIQUID_FECHAENT = @FECHA) en bloques, lo que reduce la carga de datos inicial
Aplicar filtros lo antes posible en la consulta permite al motor de base de datos reducir la cantidad de filas a procesar en etapas posteriores. Esto disminuye el uso de memoria, reduce la cantidad de uniones innecesarias y mejora significativamente el tiempo de respuesta, especialmente cuando se trabaja con tablas grandes.
En lugar de ejecutar una consulta por un rango de fechas (@LIQFECHAINI a @LIQFECHAFIN), se optó por dividir la ejecución en consultas individuales por cada día. Este cambio permite mantener la condición LIQUID_FECHAENT = @FECHA para aprovechar índices de manera más eficiente y reducir la complejidad del plan de ejecución.
Implementación en C#:

DateTime fechaInicio = parTaskParams["@LIQFECHAINI"].oParametro.ToDateTime(); DateTime fechaFin = parTaskParams["@LIQFECHAFIN"].oParametro.ToDateTime(); DataTable dtFinal = null;
for (DateTime fecha = fechaInicio; fecha <= fechaFin; fecha = fecha.AddDays(1))
{
SQL.Parametros.Agrega(“@FECHA”, eTipoDB.DATE, fecha);
if (dtFinal == null)
dtFinal = SQL.Consulta(sVentas);
else
dtFinal.Merge(SQL.Consulta(sVentas));
}

Agrupación más precisa al evitar columnas irrelevantes, mejorando el rendimiento de GROUP BY
Incluir únicamente las columnas necesarias en la cláusula GROUP BY reduce la cantidad de combinaciones distintas a evaluar y agrupar. Además, evita operaciones de hash o sort más costosas, lo que resulta en un mejor rendimiento general y una menor carga en el servidor.

Conclusión
La optimización de consultas SQL no solo mejora el rendimiento de las aplicaciones, sino que también facilita el mantenimiento y la escalabilidad del sistema. A través de prácticas como el uso de CTEs, la eliminación de joins innecesarios y la aplicación anticipada de filtros, es posible reducir significativamente el tiempo de ejecución y el consumo de recursos. Una consulta bien estructurada no solo es más eficiente, sino también más clara para futuros desarrolladores.