Re: Informix 5, greater thans, and cpu usage

->From: gtynen@interaccess.com (Gerard Tynen) ->Subject: Informix 5, greater thans, and cpu usage ->Date: 23 Mar 1994 11:00:50 -0600 ->Reply-To: gtynen@interaccess.com (Gerard Tynen) ->Organization: The second InterAccess INN server -> ->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 -> -> ...(miscellaneous relevant details omitted)... -> ->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 Gerry, I think the problem is the OR in your query. Informix seems to handle queries with ORs rather poorly. They are ALWAYS slow for me, so I avoid them whenever I can. This is just a guess, but I think that the engine is putting two separate sets of rows into the temp table you mentioned, one for each "side" of the OR, and then sorting the two sets of rows together. I suggest using DeMorgan's theorem, or some other method, to recast your OR condition into an AND condition. That should speed things up a lot. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\