Re: How to write efficient SQL Statement?

'''' Thenardier wrote: > > Hi, > > I'm new to Informix. But now i'm ask to develop a system on > an Informix platform as backend and PowerBuilder 5.0 as frontend. > I wonder how Informix interpret SQL statements. Say in a SQL > statement like: > > SELECT id, name FROM tableA > WHERE criteriaA = 'XXX' AND > criteriaB = 'YYY' AND > criteriaC = 'CCC';> > What would Informix do to this? Reading criteriaC > first then go up, or from up to down? Can anyone tell? Informix 7.xx's query optimizer is VERY intelligent and could do the query in any number of ways depending on what indexes are available, the data distribution, the exact values of the criteria being queried (yes the query path can change for different where clauses), the number of rows of data, etc. Informix 5.xx's optimizer was less intelligent but still could perform the query a number of ways depending on index availability, etc. Sorry, I cannot give you a concrete answer, even if you'd posted a full schema. The best way is to SET EXPLAIN ON and try the query for several sets of values for the criteria including outliers (ie largest value, smallest value, most numerous value, least numerous value) and combinations of outliers, with and without statistics updated. This will give you a clearer picture of what the optimizer MIGHT do with your query under various conditions, but, why care? If the data comes back fast enough who cares how the engine got it? Of course if it is not comming back fast enough then that's another story. Art S. Kagel