Re: Informix 5, greater thans, and cpu usage

Gerard Tynen (gtynen@interaccess.com) wrote: : I am trying to speed up a number of queries in SQL that seem : to be the source of some very slow response times in our : applications. The queries use a number of select statements of the : form: : SELECT rowid, cset, cacc, serialcolumn FROM gca WHERE ((CSET = "1") : AND (CACC >= " ")) OR ((CSET > "1")) ORDER BY cset, cacc The OR statement may be causing a problem. Try rephrasing the query as a UNION of the individual parts. : We have non-unique indexes on cset, cacc and so I beleive that this : index already is in the correct order we are asking for. However, : this type of query seems to take a long time (>20 seconds for 15,000 : rows in gca) and I am seeing my cpu usage going up to 100% while this : query runs, while memory usage and i/o are low. : I have a suspicion that Informix is sorting the result set even though : the index would return rows in the correct ordering, and that is why : I am seeing high cpu usage. : The output of explain says that it is using the index to satisfy all the : parts of the query, but also that it is using a temp table to do the : order by. This adds to my suspicion about a sort taking place. : so, my questions are: : Shouldn't Informix not need to do a sort, if the rows returned in : index order are the correct order for the ORDER BY? : If indeed Informix is performing this sort, would that peg my cpu : like I am observing? : Is there a way to rewrite or otherwise optimize this type of query? : Thanks in advance, : Gerry Tynen -- =========================================================================== jlumbley@netcom.com (Joe Lumbley) BancTec Service Corportation 214-450-9894 Dallas, Texas Watch for my _INFORMIX DBA SURVIVAL GUIDE_ in Fall '94 from Prentice Hall! ===========================================================================