Adding an INDEX to a CTE

You cannot index a CTE, but the approach is that the CTE can make use of the underlying indexes.

WITH cte AS (
    SELECT myname, SUM(Qty) FROM t GROUP BY myname
)
SELECT *
FROM t a JOIN cte b ON a.myname=b.myname 

In the above query, a JOIN b cannot make use of an index on t.myname because of the GROUP BY.

On the other hand,

WITH cte AS (
    SELECT myname, SUM(Qty) OVER (PARTITION BY myname) AS SumQty, 
                ROW_NUMBER() OVER (PARTITION BY myname ORDER BY myname, Qty) AS n
)
SELECT * FROM t a JOIN cte b ON a.myname=b.myname AND b.n=1 

In the latter query, a JOIN b can make use of an index on t.myname.

Another technique is to insert into a temp table instead of using a CTE You can then add an index to the temp table

I reduced a 9min query to a 3 sec query by doing this.

Some may be religiously opposed to temp tables. If this is you, feel free to click the downvote button!

for the rest of us trying to get things working... something to consider.

( I did try the top 100000 ... order by) I did not realize a time reduction.

/* The Below construct reduced my query from 2 minutes to 4 seconds */

WITH First_cte AS(
    SELECT Field1, Field2, etc
    FROM etc
    ORDER BY Field1, Field2 OFFSET 0 ROWS
),
Second_cte AS(
    SELECT Field1, Field2, etc
    FROM etc
    ORDER BY Field1, Field2 OFFSET 0 ROWS
)
SELECT
    First_cte.Field1, First_cte.Field2, etc
    FROM First_cte
    INNER JOIN Second_cte
        ON Second_cte.Field1 = First_cte.Field1
        AND Second_cte.Field2 = First_cte.Field2
    WHERE Second_cte.Field3 <> First_cte.Field3`