New post
Question
· Sep 5, 2019

CACHE SQL: Is the order of the WHERE conditions relevant

#SQL #Caché

hi,

i have two where-clauses:

a)  ... WHERE company=1 and product=7

b)  ... WHERE product=7 and company=1 

with other words the position of the where fields are swapped.

now the question is:

bring the where clauses a) and b) the same performance(queryplan) or do i have to write it in a special order???

in my point of view, there is a  parser/optimizer who cares about this, so i don't have to care about. 

Discussion (3)2
Log in or sign up to continue
Vic Sun · Sep 5, 2019

Hello Reinhard,

Using Sample.Person I just did a quick test and switching the order of the where clauses did not effect either performance or the query plan.

Some people design or generate very complex SQL queries so I'm not certain if this holds true in all cases (though I agree I would expect it to - if not it may be worth reporting to InterSystems!). If you are interested in comparing performance and query plans this is pretty easy to do from the System Explorer > SQL page in the system management portal.

Hope that helps!

0 0
Benjamin De Boe · Sep 6, 2019

InterSystems IRIS (and Caché before that) will indeed make this decision for you. The SQL optimizer will analyze all the conditions in your query and select the best query plan based on the available table statistics, which includes column selectivity. See also this article on collecting those stats with the TuneTable command.

As a matter of fact, our development team is making some exciting enhancements to the cost functions used to turn those table statistics into the actual cost estimates for the possible query plans. More about that at our upcoming Global Summit!

0 0