Informix 5, greater thans, and cpu usage
Posted in 1994
Topics: SQL Development & Query Writing
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
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