Efficient Query Group by, Having - SQL Server

Question

Efficient Query Group by, Having - SQL Server

Navigation

#1 by (2 votes)

4

I have the following query :

SELECT S.IdCliente
    ,S.NOMBRE
    ,S.JLI_SDESLINEA
    ,S.JCA_SDESCATEGORIA
    ,SUM(S.MONTO) AS MONTO
    ,COUNT(*) AS QTY
    ,S.DIA
    ,T.IDTERRITORIO
    ,T.VENDEDOR
    ,T.SUPERVISOR
    ,V.IDCLIENTE
FROM Sabana S
LEFT JOIN CLIENTES C ON S.IDCLIENTE = C.IDCLIENTE
    AND C.RC = 90
LEFT JOIN TERRITORIOS T ON C.TERRITORIO = T.IDTERRITORIO
LEFT JOIN (
    SELECT X.IDCLIENTE
    FROM SABANA X
    WHERE X.MONTO > 0
        AND X.PERIODO IN (
            201708
            ,201707
            ,201706
            )
    GROUP BY X.IDCLIENTE
    HAVING COUNT(DISTINCT X.PERIODO) = 3
    ) AS V ON V.IDCLIENTE = S.IDCLIENTE
WHERE S.MONTO > 0
    --AND V.IDCLIENTE IS NOT NULL
    AND S.PERIODO IN (
        201708
        ,201707
        ,201706
        )
GROUP BY S.IdCliente
    ,S.NOMBRE
    ,S.JLI_SDESLINEA
    ,S.JCA_SDESCATEGORIA
    ,S.DIA
    ,T.IDTERRITORIO
    ,T.VENDEDOR
    ,T.SUPERVISOR
    ,V.IDCLIENTE

When executing it takes approximately 2 minutes, which is fine, but I need to filter the result null of the column V.IDCLIENTE , then I tried to use INNER JOIN , and delayed 30 minutes, with IS NOT NULL also delayed 30 minutes approx.

Any ideas to make time efficient?

This is the execution plan of the query above:

And this is the real execution plan of the query using INNER JOIN instead of LEFT JOIN :

sql-server query rendimiento

asked by Marcelo 10.08.2017 в 23:53

source

1 answer

Equivalent in javascript for printf of C Native Storage Error, does not show the following screen - IONIC 3

score 2 · Accepted Answer

I would try to see ways to rewrite your query and review the indexes in the Sabana table (you should have one in IDCLIENTE , PERIODO ).

Anyway, you can deal with this query:

WITH CTE AS
(
    SELECT *, DENSE_RANK() OVER(PARTITION BY IDCLIENTE ORDER BY PERIODO) RN
    FROM Sabana
    WHERE MONTO > 0
    AND PERIODO IN (201708,201707,201706)
)
SELECT 
FROM CTE S
LEFT JOIN CLIENTES C 
    ON S.IDCLIENTE = C.IDCLIENTE
    AND C.RC = 90
LEFT JOIN TERRITORIOS T 
    ON C.TERRITORIO = T.IDTERRITORIO
WHERE EXISTS(SELECT 1 FROM CTE
             WHERE IDCLIENTE = S.IDCLIENTE
             AND RN = 3);