Join's and Index's

Question

Join's and Index's

dwilliscp

SSCrazy Eights

Points: 9270
More actions
January 30, 2013 at 12:22 pm

#273682

I am fuzzy on this.. so looking to see if my thoughts are correct... When looking at the execution plan, it does not ask for an index, but...
If you have two moderate (500K to 1M) tables, and both are a heap, wouldn't it help the inner join to have index's on what is being joined? If it matters, one is a temp table (#temp) so it would already exist in memory.. one would hope.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Maybe. Maybe not. Depends on the rows involved, the join type, other filters, complexity of the query, data types and a few other things. Add indexes, see if they help.

Temp tables are not necessarily memory resident, but anything that they query processor operates on will be in memory, temp or permanent, the query processor doesn't know what a disk is.

Ignoring joins, the permanent table needs a clustered index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 2

Thanks... BTW it is an inner join. I have already sent a request off to the software company to see if we can create a PK. Currently there is not one. I will see about a clustered index, if we can not get a PK put into place.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You really should have both on a permanent table. Maybe as one and the same thing, maybe different.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 4

Thanks for your help.. BTW I put a covering index into place, but the job that inserts data into that table grew from 30min to 90min (give or take a few min). So I have removed the covering index, and the run time is back down. While the difference in run time of this query is only about 2min. Given that this query only runs once per day.. I am going to leave the covering index off.

MMartin1 One Orange Chip Points: 27764 More actions · Answer 5

Maybe the clustered index that re-arranged the pages created disk fragmentation?

----------------------------------------------------