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

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

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.