0

I have a query that is running for more than three hours without completing.

I realized that the bad part is the Filter operator which has a cost of 89%.

WITH 
    FASES_NUMERADAS_CTE AS 
    (
        SELECT 
            NUM_PROCES, 
            DES_SISTEM_PROCES, 
            DAT_PRIMEIRA_FASE, 
            CLASSE_SISTEMA_ATUAL, 
            CLASSE_SISTEMA_ANTERIOR, 
            DAT_CLASSE_ATUAL, 
            ROW_NUMBER() OVER (
                PARTITION BY NUM_PROCES, DES_SISTEM_PROCES, DAT_PRIMEIRA_FASE 
                ORDER BY DAT_CLASSE_ATUAL) AS RN
        FROM #TEMP_COMPLE_10966_GERAL
    ), 
    FASES_RN_1_CTE AS 
    (
        SELECT *
        FROM FASES_NUMERADAS_CTE
        WHERE RN = 1
    )
UPDATE TC 
SET TC.CLASSE_SISTEMA_ATUAL = T2.CLASSE_SISTEMA_ATUAL 
FROM FASES_RN_1_CTE T1 
CROSS APPLY 
(
    SELECT TOP 1 T.CLASSE_SISTEMA_ATUAL 
    FROM FASES_NUMERADAS_CTE T
    WHERE T.NUM_PROCES = T1.NUM_PROCES
    AND T.DES_SISTEM_PROCES = T1.DES_SISTEM_PROCES
    AND T.DAT_PRIMEIRA_FASE = T1.DAT_PRIMEIRA_FASE
    AND T.RN > 1
    ORDER BY T.RN DESC
) AS T2 
INNER JOIN #TEMP_COMPLE_10966_GERAL TC 
    ON TC.NUM_PROCES = T1.NUM_PROCES 
    AND TC.DES_SISTEM_PROCES = T1.DES_SISTEM_PROCES 
    AND TC.DAT_PRIMEIRA_FASE = T1.DAT_PRIMEIRA_FASE 
    AND TC.DAT_CLASSE_ATUAL = T1.DAT_CLASSE_ATUAL 
    AND TC.CLASSE_SISTEMA_ANTERIOR = T1.CLASSE_SISTEMA_ANTERIOR;

As it doesn't end, I don't have the real execution plan.

https://www.brentozar.com/pastetheplan/?id=B1x_w-QeK

Does anyone have any tips to improve runtime?

4

I don't know that this will solve your all of your performance problems, but it will at least give you a better chance of figuring out which part of the query is actually slow, and may make a more complete solution obvious.

CTEs are generally useless constructs from a performance perspective.

CREATE CLUSTERED INDEX c ON 
    #TEMP_COMPLE_10966_GERAL
(
    NUM_PROCES, 
    DES_SISTEM_PROCES, 
    DAT_PRIMEIRA_FASE,
    DAT_CLASSE_ATUAL
); /*This index supports the row number function*/


SELECT 
    NUM_PROCES, 
    DES_SISTEM_PROCES, 
    DAT_PRIMEIRA_FASE, 
    CLASSE_SISTEMA_ATUAL, 
    CLASSE_SISTEMA_ANTERIOR, 
    DAT_CLASSE_ATUAL, 
    RN = 
        ROW_NUMBER() OVER 
        (
            PARTITION BY 
                NUM_PROCES, 
                DES_SISTEM_PROCES, 
                DAT_PRIMEIRA_FASE 
            ORDER BY 
                DAT_CLASSE_ATUAL
        )
INTO #FASES_NUMERADAS_CTE
FROM #TEMP_COMPLE_10966_GERAL;
/*Step one*/


SELECT 
    *
INTO #FASES_RN_1_CTE
FROM #FASES_NUMERADAS_CTE
WHERE RN = 1
/*Step two*/


UPDATE TC
    SET TC.CLASSE_SISTEMA_ATUAL = 
            T2.CLASSE_SISTEMA_ATUAL
FROM #FASES_RN_1_CTE T1
CROSS APPLY 
(
    SELECT TOP (1) 
        T.CLASSE_SISTEMA_ATUAL
    FROM #FASES_NUMERADAS_CTE T
    WHERE T.NUM_PROCES = T1.NUM_PROCES
    AND   T.DES_SISTEM_PROCES = T1.DES_SISTEM_PROCES
    AND   T.DAT_PRIMEIRA_FASE = T1.DAT_PRIMEIRA_FASE
    AND   T.RN > 1
    ORDER BY T.RN DESC
) AS T2
INNER JOIN #TEMP_COMPLE_10966_GERAL TC
    ON  TC.NUM_PROCES = T1.NUM_PROCES
    AND TC.DES_SISTEM_PROCES = T1.DES_SISTEM_PROCES
    AND TC.DAT_PRIMEIRA_FASE = T1.DAT_PRIMEIRA_FASE
    AND TC.DAT_CLASSE_ATUAL = T1.DAT_CLASSE_ATUAL
    AND TC.CLASSE_SISTEMA_ANTERIOR = T1.CLASSE_SISTEMA_ANTERIOR;
    /*Last, update*/
4

Another option uses a clustered index with the final key sorted descending:

CLUSTERED (NUM_PROCES, DES_SISTEM_PROCES, DAT_PRIMEIRA_FASE, DAT_CLASSE_ATUAL DESC)

and a dummy nonclustered columnstore index:

CREATE NONCLUSTERED COLUMNSTORE INDEX dummy 
ON #TEMP_COMPLE_10966_GERAL (NUM_PROCES) 
WHERE NUM_PROCES = 0 
AND NUM_PROCES = 1;

That columnstore index takes no time to create and never contains any rows due to the contradiction in the where clause. The point of it is to enable batch execution on SQL Server 2016.

With those indexes in place, the following does what you need:

UPDATE Q2
SET Q2.CLASSE_SISTEMA_ATUAL = Q2.LAST_CLASSE_SISTEMA_ATUAL
FROM
(
    SELECT
        Q1.*,
        LAST_CLASSE_SISTEMA_ATUAL =
            MAX(IIF(Q1.PrevRow IS NULL, Q1.CLASSE_SISTEMA_ATUAL, NULL)) OVER (
                PARTITION BY Q1.NUM_PROCES, Q1.DES_SISTEM_PROCES, Q1.DAT_PRIMEIRA_FASE 
                ORDER BY Q1.DAT_CLASSE_ATUAL DESC 
                ROWS UNBOUNDED PRECEDING),
        NextRow = 
            LEAD(Q1.NUM_PROCES) OVER (
                PARTITION BY Q1.NUM_PROCES, Q1.DES_SISTEM_PROCES, Q1.DAT_PRIMEIRA_FASE 
                ORDER BY Q1.DAT_CLASSE_ATUAL DESC)
    FROM 
    (
        SELECT
            TCG.*,
            PrevRow = 
                LAG(TCG.NUM_PROCES) OVER (
                    PARTITION BY TCG.NUM_PROCES, TCG.DES_SISTEM_PROCES, TCG.DAT_PRIMEIRA_FASE 
                    ORDER BY TCG.DAT_CLASSE_ATUAL DESC)
        FROM #TEMP_COMPLE_10966_GERAL AS TCG
    ) AS Q1
) AS Q2
WHERE
    Q2.NextRow IS NULL;

The idea there is to identify the first and last rows in each group by the fact that LAG or LEAD will return NULL for that case. The windowed MAX carries the target first row value forward to all rows in the window so we can perform a direct value update at the end.

The more natural FIRST_VALUE and LAST_VALUE are not used because those are currently not compatible with batch-mode execution (except when LAST_VALUE is logically equivalent to LAG or LEAD).

The execution plan is:

Batch mode execution plan

All operators except the scan and update run in batch mode. This ought to be a very efficient plan.

Sadly, order-preservation from a b-tree index is not yet well-supported for parallel window aggregates, so if you ask for a parallel plan, you will likely get parallel row-mode Window Spools instead of batch-mode Window Aggregates. These are still quite efficient, compared with old-style row-mode spools, but not in the same league as Window Aggregates.

4

With the clustered index already suggested in other answers on:

(NUM_PROCES, DES_SISTEM_PROCES, DAT_PRIMEIRA_FASE, DAT_CLASSE_ATUAL)

One way to express your requirement is:

UPDATE FirstRecord
SET FirstRecord.CLASSE_SISTEMA_ATUAL = LastRecord.CLASSE_SISTEMA_ATUAL
FROM 
(
    SELECT DISTINCT 
        TCG.NUM_PROCES, 
        TCG.DES_SISTEM_PROCES, 
        TCG.DAT_PRIMEIRA_FASE 
    FROM #TEMP_COMPLE_10966_GERAL AS TCG
) AS Groups
CROSS APPLY
(
    SELECT TOP (1)
        TCG2.CLASSE_SISTEMA_ATUAL
    FROM #TEMP_COMPLE_10966_GERAL AS TCG2
    WHERE
        TCG2.NUM_PROCES = Groups.NUM_PROCES
        AND TCG2.DES_SISTEM_PROCES = Groups.DES_SISTEM_PROCES
        AND TCG2.DAT_PRIMEIRA_FASE = Groups.DAT_PRIMEIRA_FASE
    ORDER BY
        TCG2.DAT_CLASSE_ATUAL ASC
) AS FirstRecord
CROSS APPLY
(
    SELECT TOP (1)
        TCG3.CLASSE_SISTEMA_ATUAL
    FROM #TEMP_COMPLE_10966_GERAL AS TCG3
    WHERE
        TCG3.NUM_PROCES = Groups.NUM_PROCES
        AND TCG3.DES_SISTEM_PROCES = Groups.DES_SISTEM_PROCES
        AND TCG3.DAT_PRIMEIRA_FASE = Groups.DAT_PRIMEIRA_FASE
    ORDER BY
        TCG3.DAT_CLASSE_ATUAL DESC
) AS LastRecord
WHERE
    FirstRecord.CLASSE_SISTEMA_ATUAL != LastRecord.CLASSE_SISTEMA_ATUAL
--OPTION (USE HINT ('ENABLE_PARALLEL_PLAN_PREFERENCE'))
;

The idea is to first find the set of groups, then the first and last rows per group. The update then sets the desired value in the 'first' row to match that found in the last. This approach works best when there are fewer, larger groups. It can still perform reasonably well with smaller average group sizes, but that is not the optimal case.

The expected execution plan shape is:

expected execution plan

The Eager Spool is required for Halloween Protection, since the update includes a self-join.

The optimizer is somewhat biased against parallel plans for nested loop joins. If you want to try a parallel update, you will likely need the undocumented query hint commented out in script above. With the hint, the plan becomes:

Parallel plan

3

It looks like your query could be vastly improved like this:

  • Updating the CTE directly, rather than rejoining
  • Using LAST_VALUE to get the partitioned data that we need, rather than rejoining again
  • I also strongly recommend an index (preferably clustered) in the following column order:
    (NUM_PROCES, DES_SISTEM_PROCES, DAT_PRIMEIRA_FASE, DAT_CLASSE_ATUAL) INCLUDE (CLASSE_SISTEMA_ATUAL)
    The first three columns can be in any order.
WITH FASES_NUMERADAS_CTE AS (
    SELECT *,
        ROW_NUMBER() OVER
          (PARTITION BY NUM_PROCES, DES_SISTEM_PROCES, DAT_PRIMEIRA_FASE
           ORDER BY DAT_CLASSE_ATUAL
          ) AS RN,
        LAST_VALUE(CLASSE_SISTEMA_ATUAL) OVER
          (PARTITION BY NUM_PROCES, DES_SISTEM_PROCES, DAT_PRIMEIRA_FASE
           ORDER BY DAT_CLASSE_ATUAL
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
          ) AS Last_CLASSE_SISTEMA_ATUAL
    FROM #TEMP_COMPLE_10966_GERAL T
)
UPDATE T
SET T.CLASSE_SISTEMA_ATUAL = T.Last_CLASSE_SISTEMA_ATUAL
FROM FASES_NUMERADAS_CTE T
WHERE T.RN = 1;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.