Menu
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!
972
As a best practice you should try to order your table join so the join that reduces the result set the most is joined first. Before we start let's add an index to the column in the table we'll be using as the join condition (you'll see more on this in a later topic).
It doesn't matter.
The join order is the order in which the tables are joined together in a multi-table SQL statement. Ideally, a plan should start with the join that eliminates the most data to minimize the amount of data carried into the subsequent joins.
Six Operations to Order: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.
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 JOINs... try to structure your query to avoid any RIGHT OUTER JOINs, 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.
163
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
