SQL Server Performance

Query plan changes radically simply adding "INSERT INTO #myTemTable"

Discussion in 'ALL SQL SERVER QUESTIONS' started by Wentu, Nov 28, 2012.

  1. Wentu New Member

    Hello

    (first of all, the title is wrong, no "INSERT", i am just talking about a SELECT INTO...sry, i cant edit the title)

    I am facing a strange behaviour in the performance of a non simple query.

    i try to keep the story short:

    1) my query involves a CTE with 4 or 5 SELECTs before the final SELECT

    2) If the final SELECT asks for all the fields and makes no group by its time is below 1sec.
    2b) If in the final SELECT I request only two fields and group on those 2 fields, the time is more than a minute
    2c) If in the final SELECT I request the same two fields and I group on the same 2 fields AND I add one more grouping field, the ex.time drops again under 1 second
    Notice that the rest of the query doesnt change a bit.

    Why is grouping for one more field better for the performance?

    3) I decide to change my global query so that it first produces a SELECT with a grouping on 3 fields (so that it is fast) and then I try to group again to obtain what i am really interested in.
    3b) What happens is: if in the final SELECT I ask for 1 or all (*) fields,no grouping, the time is less than a second and i get 50 thousands rows.
    3c) If I ask for one single field (the same of the previous attempt) and I ask to group by that field, the time is now again more than 1 minute
    The mere operation of grouping 50K rows on a field shouldnt take that much time, i suppose. The field is one of those that i create during the CTE but eventually it traces back to a field that is a PK of its table.

    4)Now the "gran-finale". I use the CTE in its fast version to create a view. The SELECT Field1 FROM myView is fast, 2 seconds, and it uses a query plan A
    4b) If i SELECT Field1 FROM myView GROUP BY Field1, it is very slow and uses a completely different query plan B
    4c) If I SELECT Field1 INTO #myTempTab FROM myView it is very slow and uses again plan B !! it is doing the exact same query as in (4), only difference is inserting the result in a temptable, but it is again using a different much slower query plan.

    Why?? How can i force this query to behave? How is this behaviour possible??

    THANKX!

    I attach the query plans for:
    Code:
    select Risorsa_Id
    --into #temptab
    from v_statiUltimiRetryDistribuzioni
    
    (SLOW is with the line uncommented)

    Here is the text of the view "v_statiUltimiRetryDistribuzioni"

    Code:
    ;WITH distribuzioni AS (
                SELECT        v.Versione_Id AS Versione_Id,
                                v.Risorsa_Id AS Risorsa_Id,
                                dp.Dispositivo_Id AS Dispositivo_Id,
                                d .Distinta_Id AS Distinta_Id,
                                p.statoPacchetto AS StatoPacchetto,
                                ROW_NUMBER() OVER (PARTITION BY v.Versione_Id, dp.Dispositivo_Id, d .Distinta_Id ORDER BY p.sottomissione) AS N
                    FROM        Versioni AS v INNER JOIN
                                      TagsApplicati AS ta ON v.Risorsa_Id = ta.Taggato_Id INNER JOIN
                                      Dispositivi AS dp ON ta.NomeTag = dp.MACAddress INNER JOIN
                                      Colli AS cl ON v.Versione_Id = cl.Versione_Id INNER JOIN
                                      Distinte d ON d .ColloSpedizione_Id = cl.Collo_Id INNER JOIN
                                      SottoDistinte sd ON d .Distinta_Id = sd.Distinta_Id INNER JOIN
                                      Pacchetti p ON sd.Pacchetto_Id = p.Pacchetto_Id INNER JOIN
                                      ComandiTrasporto ct ON ct.Pacchetto_Id = p.Pacchetto_Id AND ct.Dispositivo_Id = dp.Dispositivo_Id
                    WHERE    p.Sottomissione > '20120601'
                    GROUP BY v.Risorsa_Id,
                            v.Versione_Id,
                            dp.Dispositivo_Id,
                            d .Distinta_Id,
                            p.statoPacchetto,
                            p.sottomissione
    ), indiciUltimiRetry AS(
                SELECT    Risorsa_Id,
                            Versione_Id,
                            Dispositivo_Id,
                            Distinta_Id,
                            MAX(N) ultimoRetry
                  FROM          distribuzioni
                  GROUP BY Risorsa_Id,
                            Versione_Id,
                            Dispositivo_Id,
                            Distinta_Id
    ), StatiUltimiRetry AS(
                SELECT    dis.Risorsa_Id,
                            dis.Versione_Id,
                            dis.Dispositivo_Id,
                            dis.Distinta_Id,
                            dis.StatoPacchetto,
                            iur.ultimoRetry
                FROM      distribuzioni dis INNER JOIN
                                    indiciUltimiRetry iur ON dis.Risorsa_Id = iur.Risorsa_Id
                                                            AND dis.Versione_Id = iur.Versione_Id
                                                            AND dis.Dispositivo_Id = iur.Dispositivo_Id
                                                            AND dis.Distinta_Id = iur.Distinta_Id
                                                            AND dis. N = iur.ultimoRetry
    ), NumDistribuzioniXperVersione AS(
                SELECT sur.Risorsa_Id AS Risorsa_Id,
                        sur.versione_Id,
                        SUM(CASE WHEN (sur.StatoPacchetto = 10) THEN (1) ELSE (0) END) AS NumDistribuzioniC,
                        SUM(CASE WHEN (sur.StatoPacchetto = 9) THEN (1)  ELSE (0) END) AS NumDistribuzioniE,
                        SUM(CASE WHEN (sur.StatoPacchetto = 8) THEN (1) ELSE (0) END) AS NumDistribuzioniA,
                        SUM(CASE WHEN (sur.StatoPacchetto < 8 OR sur.StatoPacchetto > 10) THEN (1) ELSE (0) END) AS NumAltriEsiti
                  FROM          StatiUltimiRetry sur
                  GROUP BY sur.Risorsa_Id,
                            sur.Versione_Id
    ), NumDistribuzioniXperRisorsa AS(
                                        SELECT    Risorsa_Id,
                                                    SUM(NumDistribuzioniC) AS NumDistribuzioniC,
                                                    SUM(NumDistribuzioniE) AS NumDistribuzioniE,
                                                    SUM(NumDistribuzioniA) AS NumDistribuzioniA,
                                                    SUM(NumAltriEsiti) AS NumAltriEsiti
                                          FROM          NumDistribuzioniXperVersione AS ndxpv
                                          GROUP BY Risorsa_Id
                                        )
        SELECT    sur.Risorsa_Id,
                    sur.Versione_Id,
                    sur.Dispositivo_Id,
                    sur.Distinta_Id,
                    sur.StatoPacchetto,
                    sur.UltimoRetry,
                    ndxr.NumDistribuzioniC,
                    ndxr.NumDistribuzioniE,
                    ndxr.NumDistribuzioniA,
                    ndxr.NumAltriEsiti
        FROM        StatiUltimiRetry sur INNER JOIN
                                NumDistribuzioniXperRisorsa ndxr ON sur.Risorsa_Id = ndxr.Risorsa_id
  2. Wentu New Member

    This issue has been solved this way:
    1) Query engine has some problems dealing with CTE with many selects and groupings at different level.
    2) Better to split the CTE in 2 CTEs and using a #temptable in the middle so that the engine doesnt get confused.
  3. Luis Martin Moderator

    Welcome to the forums and thank for sharing.:)

Share This Page