CODEWITHSUNDEEP
sqlsql-serversql-server-2005jointemp-tables
Malik Daud Ahmad Khokhar
Malik Daud Ahmad Khokhar

Reputation: 13720

Speeding up temp table joins in SQL Server

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?

Upvotes: 4

Views: 2408

Answers (4)

Quassnoi
Quassnoi

Reputation: 425311

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?

Upvotes: 3

DForck42
DForck42

Reputation: 20327

depending on what you're doing you could probably avoid the temp tables altogether and have a set-based solution (which will run a lot quicker and scale better), but that's hard to know without any idea of what your query is.

Upvotes: 1

Jan Zich
Jan Zich

Reputation: 15323

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

Upvotes: 6

Ross McFarlane
Ross McFarlane

Reputation: 4254

I have a suspicion that your whole query might want looking at. There may be another way to skin this particular rabbit.

Could you leave some more detail?

Upvotes: 1

Related Questions