My google-fu and so-fu is failing me here, so I might as well ask.
I have many queries with multiple joins in them.
Within one query, I am joining header/item/details together, as well as looking up various bits of information for these records.
When joining, I try to keep things in order of how their related. E.g.: My header has two lookup tables, so I'll join to those before joining to my items table.
Is that correct?
Is it better to join to larger tables before lookup tables? Or vice-versa?
Should I use a loop
hint when joining to small tables, and a merge
hint when joining to openrowsets?
I'm sure the answer is "it depends", but some general guidelines to joining effectively and efficiently would be very helpful. Thanks!
EDIT: Based on your questions (and Kirk Woll's) comment, you should understand that the order of your joins is aesthetic, and does not directly impact the resulting execution plan. The query optimizer will perform the joins in the most efficient order, and use the appropriate join operation the vast majority of the time without any hints needed.
When it comes the the aesthetics of your join order this is a little subjective, but I would say join your tables together in whatever order makes logical sense when reading through the query... if you start with a @HeaderId, start with that table, and then JOIN
to the children tables:
FROM
Header h
JOIN Items i ON h.HeaderId = i.HeaderId
JOIN Details d ON i.ItemId = d.ItemId
WHERE
h.HeaderId = @HeaderId
But, if you started your query with a @DetailId, I would join in the opposite order.
FROM
Details d
JOIN Items i ON d.ItemId = i.ItemId
JOIN Header h ON i.HeaderId = h.HeaderId
WHERE
d.DetailId = @DetailId
However, that's subjective and just my personal preference.
It becomes less subjective when you start to include OUTER JOIN
s... try to structure your query to avoid any RIGHT OUTER JOIN
s, and instead use LEFT OUTER JOIN
's.
Don't use join hints by default... in fact you'll almost never use them. The query optimizer does a very good job of choosing the optimal execution plan. I've only encountered a single instance where I needed to provide a join hint to improve the plan... it helped drastically on the server that the query was running on at the time, but when the database was migrated to a different server, my join hint destroyed the performance of the query. So, to reiterate, it is usually a bad idea to provide join hints.