FW: Explain (more is better?)
OPTCOMPIND = 0 proved to be VERY bad on our system, and we run OLTP. We made some onconfig changes, but after changing OPTCOMPIND to 2, we had a very drastic improvement in speed. Dirk Reach Technologies -----Original Message----- From: owner-informix-list@iiug.iiug.org [mailto:owner-informix-list@iiug.iiug.org]On Behalf Of David Williams Sent: Monday, December 11, 2000 3:59 AM To: informix-list@iiug.org Subject: Re: Explain (more is better?) In article <fApY5.29470$eT4.2358818@nnrp3.clara.net>, John Berry <johnberry@clara.net> writes >We have a large number of tables > 700 with a large majority in the range >of > 1GB > >Some of the queries on the database can take a number of minutes especially >when >multiple joins are being performed. > >Now I have always assumed the lower the cost of query the better :-) > >However I am starting to doubt this when I run a low cost query on a number >of frequent hit tables it can take upto 2 mins to return data >If I re-structure the query the cost goes up to 2500 but data is returned in >seconds ? > Run you run the full update statistics? Perhaps the stat are out of date. >Would I be right in assuming the cost is based on use of indexes and amount >of data retrieved and not a lot else so a high cost query is not always bad. > >and if so what about OPTCONF hmm 0,1 or 2 > OPTCOMPIND = 0 favors indexes and should be used on OLTP systems. >:-) > > -- David Williams
In the year of Our Lord Mon, 11 Dec 2000 10:22:04 +0200, "Dirk Moolman" <dirkm@reach.co.za> broke a vow of silence to utter: > >OPTCOMPIND = 0 proved to be VERY bad on our system, and we run OLTP. We >made some onconfig changes, but after changing OPTCOMPIND to 2, we had a >very drastic improvement in speed. Oh? Sounds like you might be missing something... >-----Original Message----- >From: owner-informix-list@iiug.iiug.org >[mailto:owner-informix-list@iiug.iiug.org]On Behalf Of David Williams >Sent: Monday, December 11, 2000 3:59 AM >To: informix-list@iiug.org >Subject: Re: Explain (more is better?) > > >In article <fApY5.29470$eT4.2358818@nnrp3.clara.net>, John Berry ><johnberry@clara.net> writes >>We have a large number of tables > 700 with a large majority in the range >>of > 1GB >> >>Some of the queries on the database can take a number of minutes especially >>when >>multiple joins are being performed. >> >>Now I have always assumed the lower the cost of query the better :-) >> >>However I am starting to doubt this when I run a low cost query on a >number >>of frequent hit tables it can take upto 2 mins to return data >>If I re-structure the query the cost goes up to 2500 but data is returned >in >>seconds ? >> > > Run you run the full update statistics? Perhaps the stat are out of > date. > >>Would I be right in assuming the cost is based on use of indexes and amount >>of data retrieved and not a lot else so a high cost query is not always >bad. >> >>and if so what about OPTCONF hmm 0,1 or 2 >> > > OPTCOMPIND = 0 favors indexes and should be used on OLTP systems. > >>:-) >> >> > >-- >David Williams >