FW: FW: Explain (more is better?)

If we do, everyone else also missed it. We have a case (related to performance) open with Tech Support now for about 4 months. We've looked at HP patches, configuration changes, kernel parameters - the works ....... -----Original Message----- From: owner-informix-list@iiug.iiug.org [mailto:owner-informix-list@iiug.iiug.org]On Behalf Of Obnoxio The Clown Sent: Monday, December 11, 2000 4:00 PM To: informix-list@iiug.org Subject: Re: FW: Explain (more is better?) 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 >

In the year of Our Lord Tue, 12 Dec 2000 10:02:36 +0200, "Dirk Moolman" <dirkm@reach.co.za> broke a vow of silence to utter: >If we do, everyone else also missed it. We have a case (related to >performance) open with Tech Support now for about 4 months. We've looked at >HP patches, configuration changes, kernel parameters - the works ....... Mmmmm....well, I've run plenty of different OLTP systems on HP using OPTCOMPIND 0, and never had a problem. Have you guys ever done a full dbexport/initialise/dbimport? >-----Original Message----- >From: owner-informix-list@iiug.iiug.org > >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 >> >