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.
----------------------------------------------------