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?
+5
A:
I believe you can create indexes on temporary tables as on any other tables.
Jan Zich
2009-08-25 07:43:35
I could definitely try that
Daud
2009-08-25 07:44:39
This is a good idea - as well as specifying a primary key on your temp tables.
pjp
2009-08-25 09:08:22
I am not sure if having an index on 25k will give you any performance benefit. If I recall there seems to be a threshold before SQL Server will start to use an index. Otherwise it is going to table scans because it is quick than traversing and index each time. I would bench mark the two.
uriDium
2009-08-25 09:52:57
The key thing you get from the indexes is the data is now "pre-sorted". Done properly, the optimizer should be able to use a merge join or a nested loop instead of a hash match.
Philip Kelley
2009-08-25 16:44:48
A:
Hi Daud,
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?
rossmcf
2009-08-25 07:51:07
I'm not known for my query writing skills. But that indexing advice seems to have worked. I created a clustered index on the joining columns of one temp table and its returning results quite quickly now.
Daud
2009-08-25 08:00:09
A:
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.
DForck42
2009-08-25 16:11:04
+1
A:
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?
Quassnoi
2009-08-25 16:32:35