Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

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,
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of marrowyung
marrowyung

ASKER

"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  ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ?
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.
"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 ?
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.
"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. "?
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. "?
ok, then I am wondering how can we make sure that data distribution pattern is correct ?
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.
"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 !
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial