Speeding up temp table joins in SQL Server

Question

I have 2 temp tables #temp1 and #temp. Both have a key and date columns. Both have around 25k rows. And I'm left joining them on the basis of the key and date which are unique on all rows. It's taking around 4 minutes for this join to complete. Is there any way to speed it up or any alternative methods?

Answer 1

I believe you can create indexes on temporary tables as on any other tables.

Answer 2

If your join of 25k tables takes 4 minutes, there's something wrong with it.

Most probably you put a wrong JOIN condition which leads to a cartesian join (or something close to it), which results in 25k * 25k = 625M records returned.

This can take 4 minutes indeed if not more, but I don't think it was what you wanted.

Probably you have DISTINCT / GROUP BY clauses in your query, which makes the query to return correct resultset but in a non-optimal way.

Could you please post your query so that I can tell the exact reason?