如何优化包含子查询的执行缓慢SQL查询语句

问题背景 #

原SQL查询因包含多个关联子查询导致执行效率极低，这类子查询会对主查询的每一行数据重复执行，大幅增加数据库负载。

原始查询语句 #

SELECT DISTINCT 
     dbo.Ventas.IdVenta, dbo.Ventas.CodigoVenta, dbo.Ventas.Fecha_Alta, dbo.M_Operadores.Nombre + ' ' + dbo.M_Operadores.Apellidos AS Operador, SUBSTRING(dbo.Ventas.Cliente, 0, CHARINDEX(CHAR(13), dbo.Ventas.Cliente)) 
     AS Cliente, dbo.M_Centros.Centro,
         (SELECT        SUM(LineaImporte) AS Expr1
           FROM            dbo.Ventas_Detalle
           WHERE        (IdVenta = dbo.Ventas.IdVenta)) - dbo.Ventas.Redondeo AS Importe,
         (SELECT        ISNULL(SUM(dbo.Movimientos_Caja_Detalle.Ajuste), 0) AS Ajuste
           FROM            dbo.Movimientos_Caja_Detalle INNER JOIN
                                     dbo.Movimientos_Caja ON dbo.Movimientos_Caja_Detalle.IdMovimientoCaja = dbo.Movimientos_Caja.IdMovimientoCaja
           WHERE        (dbo.Movimientos_Caja.IdVenta = dbo.Ventas.IdVenta)) AS Ajuste,
         (SELECT        ISNULL(SUM(Movimientos_Caja_Detalle_2.Importe), 0) AS Cobrado
           FROM            dbo.Movimientos_Caja_Detalle AS Movimientos_Caja_Detalle_2 INNER JOIN
                                     dbo.Movimientos_Caja AS Movimientos_Caja_2 ON Movimientos_Caja_Detalle_2.IdMovimientoCaja = Movimientos_Caja_2.IdMovimientoCaja
           WHERE        (Movimientos_Caja_2.IdVenta = dbo.Ventas.IdVenta) OR
                                     (Movimientos_Caja_2.CodigoSobre = dbo.Ventas.CodigoSobre) AND (Movimientos_Caja_2.IdTipoMov_Caja = 7)) AS Cobrado,
         (SELECT        SUM(LineaImporte) AS Expr1
           FROM            dbo.Ventas_Detalle AS Ventas_Detalle_1
           WHERE        (IdVenta = dbo.Ventas.IdVenta)) - dbo.Ventas.Redondeo -
         (SELECT        ISNULL(SUM(Movimientos_Caja_Detalle_1.Importe), 0) AS Cobrado
           FROM            dbo.Movimientos_Caja_Detalle AS Movimientos_Caja_Detalle_1 INNER JOIN
                                     dbo.Movimientos_Caja AS Movimientos_Caja_1 ON Movimientos_Caja_Detalle_1.IdMovimientoCaja = Movimientos_Caja_1.IdMovimientoCaja
           WHERE        (Movimientos_Caja_1.IdVenta = dbo.Ventas.IdVenta) OR
                                     (Movimientos_Caja_1.CodigoSobre = dbo.Ventas.CodigoSobre) AND (Movimientos_Caja_1.IdTipoMov_Caja = 7)) AS Pendiente,
         (SELECT        Importe AS expr1
           FROM            dbo.Vales
           WHERE        (IdVentaEmision = dbo.Ventas.IdVenta)) AS Vale, dbo.Ventas.CodigoSobre, dbo.Ventas.FechaTerminado, dbo.Ventas.IdCentro_Alta AS IdCentro, dbo.Ventas.FechaEntregado, dbo.Ventas.IdOperador, 
     dbo.Ventas.FacturaVenta, dbo.Ventas.FechaFactura, dbo.Ventas.EstadoFactura, dbo.Ventas.TipoFactura
FROM         dbo.Ventas INNER JOIN
             dbo.M_Operadores ON dbo.Ventas.IdOperador = dbo.M_Operadores.IdOperador INNER JOIN
             dbo.M_Centros ON dbo.Ventas.IdCentro_Alta = dbo.M_Centros.IdCentro

初步优化（表别名简化） #

已将全表名替换为短别名，提升代码可读性，但未解决子查询的核心性能问题：

SELECT  
    V.IdVenta, V.CodigoVenta, V.Fecha_Alta, MO.Nombre + ' ' + MO.Apellidos AS Operador, SUBSTRING(V.Cliente, 0, CHARINDEX(CHAR(13), V.Cliente)) 
    AS Cliente, MC.Centro,
        (SELECT        SUM(LineaImporte) AS Expr1
        FROM            Ventas_Detalle
        WHERE        (IdVenta = V.IdVenta)) - V.Redondeo AS Importe,
        (SELECT        ISNULL(SUM(Movimientos_Caja_Detalle.Ajuste), 0) AS Ajuste
        FROM            Movimientos_Caja_Detalle INNER JOIN
                                    Movimientos_Caja ON Movimientos_Caja_Detalle.IdMovimientoCaja = Movimientos_Caja.IdMovimientoCaja
        WHERE        (Movimientos_Caja.IdVenta = V.IdVenta)) AS Ajuste,
        (SELECT        ISNULL(SUM(Movimientos_Caja_Detalle_2.Importe), 0) AS Cobrado
        FROM            Movimientos_Caja_Detalle AS Movimientos_Caja_Detalle_2 INNER JOIN
                                    Movimientos_Caja AS Movimientos_Caja_2 ON Movimientos_Caja_Detalle_2.IdMovimientoCaja = Movimientos_Caja_2.IdMovimientoCaja
        WHERE        (Movimientos_Caja_2.IdVenta = V.IdVenta) OR
                                    (Movimientos_Caja_2.CodigoSobre = V.CodigoSobre) AND (Movimientos_Caja_2.IdTipoMov_Caja = 7)) AS Cobrado,
        (SELECT        SUM(LineaImporte) AS Expr1
        FROM            Ventas_Detalle AS Ventas_Detalle_1
        WHERE        (IdVenta = V.IdVenta)) - V.Redondeo -
        (SELECT        ISNULL(SUM(Movimientos_Caja_Detalle_1.Importe), 0) AS Cobrado
        FROM            Movimientos_Caja_Detalle AS Movimientos_Caja_Detalle_1 INNER JOIN
                                    Movimientos_Caja AS Movimientos_Caja_1 ON Movimientos_Caja_Detalle_1.IdMovimientoCaja = Movimientos_Caja_1.IdMovimientoCaja
        WHERE        (Movimientos_Caja_1.IdVenta = V.IdVenta) OR
                                    (Movimientos_Caja_1.CodigoSobre = V.CodigoSobre) AND (Movimientos_Caja_1.IdTipoMov_Caja = 7)) AS Pendiente,
        (SELECT        Importe AS expr1
        FROM            Vales
        WHERE        (IdVentaEmision = V.IdVenta)) AS Vale, V.CodigoSobre, V.FechaTerminado, V.IdCentro_Alta AS IdCentro, V.FechaEntregado, V.IdOperador, 
    V.FacturaVenta, V.FechaFactura, V.EstadoFactura, V.TipoFactura
FROM            Ventas V INNER JOIN
                M_Operadores MO ON V.IdOperador = MO.IdOperador INNER JOIN
                M_Centros MC ON V.IdCentro_Alta = MC.IdCentro

深度优化方案（替换关联子查询为JOIN+聚合） #

核心思路是将重复执行的子查询改为预先聚合的JOIN查询，避免对主表每行重复计算，大幅降低执行时间：

SELECT  
    V.IdVenta, 
    V.CodigoVenta, 
    V.Fecha_Alta, 
    MO.Nombre + ' ' + MO.Apellidos AS Operador, 
    SUBSTRING(V.Cliente, 0, CHARINDEX(CHAR(13), V.Cliente)) AS Cliente, 
    MC.Centro,
    ISNULL(VD.TotalLineaImporte, 0) - V.Redondeo AS Importe,
    ISNULL(MCA.TotalAjuste, 0) AS Ajuste,
    ISNULL(MCC.TotalCobrado, 0) AS Cobrado,
    ISNULL(VD.TotalLineaImporte, 0) - V.Redondeo - ISNULL(MCC.TotalCobrado, 0) AS Pendiente,
    VL.Importe AS Vale, 
    V.CodigoSobre, 
    V.FechaTerminado, 
    V.IdCentro_Alta AS IdCentro, 
    V.FechaEntregado, 
    V.IdOperador, 
    V.FacturaVenta, 
    V.FechaFactura, 
    V.EstadoFactura, 
    V.TipoFactura
FROM Ventas V 
INNER JOIN M_Operadores MO ON V.IdOperador = MO.IdOperador 
INNER JOIN M_Centros MC ON V.IdCentro_Alta = MC.IdCentro
-- 预聚合Ventas_Detalle的总和，替代重复子查询
LEFT JOIN (
    SELECT IdVenta, SUM(LineaImporte) AS TotalLineaImporte
    FROM Ventas_Detalle
    GROUP BY IdVenta
) VD ON V.IdVenta = VD.IdVenta
-- 预聚合Ajuste的总和，替代子查询
LEFT JOIN (
    SELECT MC.IdVenta, ISNULL(SUM(MCD.Ajuste), 0) AS TotalAjuste
    FROM Movimientos_Caja MC
    INNER JOIN Movimientos_Caja_Detalle MCD ON MC.IdMovimientoCaja = MCD.IdMovimientoCaja
    GROUP BY MC.IdVenta
) MCA ON V.IdVenta = MCA.IdVenta
-- 预聚合Cobrado的总和，替代重复的OR条件子查询
LEFT JOIN (
    SELECT 
        COALESCE(MC.IdVenta, V.IdVenta) AS 关联IdVenta,
        ISNULL(SUM(MCD.Importe), 0) AS TotalCobrado
    FROM Movimientos_Caja MC
    INNER JOIN Movimientos_Caja_Detalle MCD ON MC.IdMovimientoCaja = MCD.IdMovimientoCaja
    LEFT JOIN Ventas V ON MC.CodigoSobre = V.CodigoSobre AND MC.IdTipoMov_Caja = 7
    WHERE MC.IdVenta IS NOT NULL OR (MC.CodigoSobre IS NOT NULL AND MC.IdTipoMov_Caja = 7)
    GROUP BY COALESCE(MC.IdVenta, V.IdVenta)
) MCC ON V.IdVenta = MCC.关联IdVenta
-- 关联Vales表，替代子查询
LEFT JOIN Vales VL ON V.IdVenta = VL.IdVentaEmision

优化说明 #

1. 消除重复计算：原查询中多次重复计算Ventas_Detalle的总和，优化后仅聚合一次，通过JOIN关联主表。
2. 子查询转预聚合JOIN：将原本逐行执行的关联子查询改为预先分组聚合的临时结果集，数据库只需执行一次聚合操作，而非主表每行都执行一次。
3. 简化OR条件处理：针对CodigoSobre和IdTipoMov_Caja=7的关联逻辑，在预聚合时统一处理，避免主查询中重复判断。
4. 索引优化建议：确保以下字段存在索引，进一步提升JOIN和聚合效率：
   • Ventas(IdVenta, CodigoSobre, IdOperador, IdCentro_Alta)
   • Ventas_Detalle(IdVenta, LineaImporte)
   • Movimientos_Caja(IdVenta, CodigoSobre, IdTipoMov_Caja, IdMovimientoCaja)
   • Movimientos_Caja_Detalle(IdMovimientoCaja, Ajuste, Importe)
   • Vales(IdVentaEmision, Importe)

内容的提问来源于stack exchange，提问作者AngelPatxi