Joins and Indexes - need indexes on both sides of a join?
-
A recent procedure tuning exercise raised a question from one
of our analysts : When joining from a main table to several
ref tables, is performance enhanced to have indexes on the lookup column(s) of the main table?
This simulates the moderately complex structure of the query
in question:
select x.key, x.description
into #temp1 x
from someDB.dbo.refentries
create index mytempindex on #temp1 (key)
Select
a.col1, a.col2, b.col11, c.col22
from dbo.MainTable a
left join dbo.otherreftable b
on a.otherrefkey = b.key
left join #temp1 c
on a.refentrykey = c.key
In the above sample, would there be an advantage to having indexes on the otherrefkey and refentrykey columns in MainTable?
As a secondary question, will SQL server be able to use the index on the temp table effectively? With no stats available, will the optimizer recognize the index (even with a hint) or ignore it?
Thanks for any opinions, especially those that make sense
-
You might get an advantage by having an index on both columns, but I think that you might be better of with a composite index on both Key values, especially if you are most often querying with joins to both columns. You really shouldn't index based on a single query, but should evaluate your normal workload to determine what indexes would bring the most bang for the buck.
An index on a temp table can be used because statistics are created when you create the index. The optimizer may still choose not to use it based on the query, especially if doing an index seek or scan may require a bookmark/key lookup if you are returning more columns from the table than just the indexed column.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
You may get benefits from indexes, you may not. It depends on what kind of join SQL uses. Test and see. Even if you do, they probably won't be large gains with no filters in that query
Temp tables do have stats, however often the cost of building the index outweighs the savings you get from using the index. Again test, but I lean towards not putting nonclustered indexes on temp tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Just make sure you go to the execution plan to understand which of the indexes you have in place are being used and how they're being used. That can act as a guide for what you need to index.
Most of time a foreign key relationship is built between the primary key or a unique constraint in one table, and a column or columns in the other. In that case, there's already an index in the first table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
In the above sample, would there be an advantage to having indexes on the otherrefkey and refentrykey columns in MainTable?
No, except in perhaps extraordinarily rare cases.
As a secondary question, will SQL server be able to use the index on the temp table effectively? With no stats available, will the optimizer recognize the index (even with a hint) or ignore it?
No. Cluster the temp table on that key, then SQL could use it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply