MMartin1 (4/17/2015)
then I tried it with temp tables (two temp tables then the join) and boom, total execution goes way way down.
not sure what is going on here but I guess I should be happy with the improvement.
Maybe the temp database is on a faster drive? Plus it likely makes your code more readable as well to the next person that may have to maintain it. And it makes it easier to slip in comments to tag what each temp table is.
If temp tables make it faster, then following from Gail's always on the money suggestions (i.e. out of date stats), Probably the temp tables have up to date stats since they were created in the query.
This thread has already been answered by Gail when she blamed stats due to a query hint outperforming the optimiser.
I move that the question creator mark this one as answered by Gail.