CACHE SQL: Is the order of the WHERE conditions relevant

Primary tabs

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. 

Answers

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!

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!

Usually, I follow the order of indexes or column selectivity (this is a general rule in all databases). For large tables or complex queries, this can influence. However, it seems that the Caché SQL optimizer chooses the best query plan.

In general the sequence of conditions in the WHERE clause is irrelevant .

Though: you may have tables that are in fact Globals in disguise.
What I mean is they don't have a Cache generated ID key but a compound Idkey of properties.

e.g. Index idkey on (Country,SSN) [ IdKey]  ;

A structure like this is mostly old fashioned MUMPS style DB design, and worth of lot of surprises. Especially with large tables.
Suggestion for such case:
Change the design to a more future oriented one. Eg. to take advantage of Bitmaps in Caché or of sharding in ISIS