June 26, 2014 at 6:15 pm
if 3-4 tables join, does the order of table join in from clause matter? or the sql server will figure out the best execution plan for it?
Another similar question, does the order of predicates in where clause matter? From the below query, if fieldC is clustered indexed, fieldB is nonclustered indexed and no index on fieldA, if the query will be executed in order of predicate 3.2.1, it will be faster. I guess it will first to do seek based on predicate 3 and predicate2, then do scan for the selected result. I would think sql server should be smart enough to figure out the best execution plan. The order doesn't matter, Am I right?
select fieldlist
from tableName
where fieldA='some0' and fieldB='some1' and fieldC='some2'
June 26, 2014 at 6:27 pm
Dennisql (6/26/2014)
if 3-4 tables join, does the order of table join in from clause matter? or the sql server will figure out the best execution plan for it?
Under most circumstances, it won't matter. Nested Joins are a particular syntax control that order will matter for (logically, not necessarily the way the optimizer runs). I've also seen queries that were on the edge of a timeout for generating the plan to care about it, but that was a query with 20+ joins. So, "it depends". 98% of the time though, no it doesn't.
Another similar question, does the order of predicates in where clause matter?
No.
I would think sql server should be smart enough to figure out the best execution plan. The order doesn't matter, Am I right?
Most of the time, yes. On very rare occassions or against extremely large/complex underlying datasets it may need a nudge, things like OPTIMIZE FOR when you're getting odd plans and you know there's a better one you can trigger via certain parameters.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 27, 2014 at 2:51 pm
does the order of predicates in where clause matter?
Not generally. But CASE WHEN conditions are always evaluated serially (as far as returning a result is concerned), so in that specific case it could potentially matter.
CASE WHEN col1 = ... THEN ...
WHEN EXISTS(SELECT ... FROM ... WHERE ...) THEN ...
WHEN (SELECT ... FROM ... JOIN ... JOIN ...) THEN ...
In that type of situation, it might matter.
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 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply