order of join operation

Dear all,

I keep hearing that the order of SQL server join operation does matter to the tuning of query, is that right?

what is the theory behind and please show me some example of it which gives the effect before and after, tks,
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
No, that's not correct. The query performance (matter of tuning) only depends on the query plan. The query plan is compiled by the query optimizer based cost.

The only things I know:

1.  The FORCE ORDER query hint.
2. Forcing Query Plans

But strictly speaking: These do not depend on the order of the join in the SQL statement alone.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"But strictly speaking: These do not depend on the order of the join in the SQL statement alone. "

ok it just depends on cost ! right , very simple again.

tks.

but will SQL server AUTOMATICALLY make decision on the BEST JOIN ORDER  ?
Vikas GargAssociate Principal EngineerCommented:
Hello,


No, the JOIN by order is changed during optimization.

The only caveat is the Option FORCE ORDER which will force joins to happen in the exact order you have them specified.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

marrowyungSenior Technical architecture (Data)Author Commented:
Vikas Garg,

"No, the JOIN by order is changed during optimization.
"
so it depends on the JOIN cost again, right? the lost cost one will execution first?

"The only caveat is the Option FORCE ORDER which will force joins to happen in the exact order you have them specified. "

anyone will do this and why ? when the query optimizer is not doing better than developers then we use the force hints ?
ste5anSenior DeveloperCommented:
Sometimes the query optimizer is wrong, even when the used statistics are correct. It maybe just a case of where the data distribution pattern simply leads to miss estimates when the costs are calculated.
marrowyungSenior Technical architecture (Data)Author Commented:
"Sometimes the query optimizer is wrong, "

good statement, it is not perfect, but how can we know, as a DBA we can only trust it, right?

"the data distribution pattern "

this is what the statistic is going to update to include that ,right? so statistics can be wrong some time ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
I keep hearing that the order of SQL server join operation does matter to the tuning of query, is that right?
Where are you hearing does stuffs from?

good statement, it is not perfect, but how can we know, as a DBA we can only trust it, right?
No. As an experiencied DBA should never trust on nothing but analyze everything with a critical attitude specially when the performance isn't good.

so statistics can be wrong some time ?
Only if we don't use them properly.
marrowyungSenior Technical architecture (Data)Author Commented:
"Where are you hearing does stuffs from?"

so this automatically tells me that this is wrong.

"Only if we don't use them properly. "

update statistic at least weekly already good enough? the same reason why ste5an said: "It maybe just a case of where the data distribution pattern simply leads to miss estimates when the costs are calculated. "?
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's mostly because of this ---> "It maybe just a case of where the data distribution pattern simply leads to miss estimates when the costs are calculated. "?
marrowyungSenior Technical architecture (Data)Author Commented:
ok, then I am wondering how can we make sure that data distribution pattern is correct ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you use UPDATE STATISTICS WITH FULLSCAN option then you know that distribution pattern is correct.
Bad side of that is if the table is very large. In those cases is better to give a SAMPLE percentage.
marrowyungSenior Technical architecture (Data)Author Commented:
"Bad side of that is if the table is very large"

so if this is the case, special when system runs for a long time, table will get large and we all experience this from time to time ? amazing !
Vitor MontalvãoMSSQL Senior EngineerCommented:
The FULL SCAN or SAMPLE rows are only used at the moment of the UPDATE STATISTICS command so when the table grows the statistics would became more and more out of date and that's why is necessary to run UPDATE STATISTICS periodically.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.