Optimizer taking too long to determine query path

I "aquired" another Informix database recently. It seems that the answer to past performance problems were to throw indexes at it. The result is a database that is less than 2 G-bytes with about 10 tables with 5 - 25 indexes per table (10 - 30 columns per table). Users were complaining about performance... so I looked at some queries that were taking waaaayyy too long to run (> 12 hours). One particular query was taking about 11 hours to run... it involved 5 tables with and/or/matches (query-tool generated). I captured the query and ran it with "set optimization low" and it ran in 35 seconds. I encountered similar results with other queries. It seems obvious that the optimizer is taking up all that time trying to determine the most efficient way to get at the data (I'm still fairly new to Informix ---- so if anyone has any other thoughts, please let me know!!!!) I did some reading to try to determine what I could do about it. There were some suggestions about adding WHERE conditions to force it to take a particular index path... and this does work for certain queries. I can't tell my users to use "set optimization low" all the time since the majority of queries are generated by 3rd party query tools. Also it doesn't ALWAYS help, of course. I also can't expect them to add bogus WHERE conditions or select their tables in a certain order... how could they even know what would work best???? We are planning to purge out unneccessary indexes... but will this help with queries containing > 4 tables????? I have to also be careful about tuning the ONCONFIG since I have 8 other applications (>1000 users) on this server (which is a Data Warehouse/Read Only server for the most part - 95%). ANY suggestions or thoughts would be VERY welcome!!!! -- Posted via Talkway - http://www.talkway.com Surf Usenet at home, on the road, and by email -- always at Talkway.

Has an UPDATE STATISTICS been run on the database recently. If it's quite a dynamic database, or the stats have not been updated recently, the optimiser will be unable to take full advantage of appropriate indexes. Just a thought, as this is something that could make a dramatic difference for little effort. Neil Truby Londis Stores Hampton Hill, UK katherinec wrote in message ... >I "aquired" another Informix database recently. It seems that the >answer to past performance problems were to throw indexes at it. The >result is a database that is less than 2 G-bytes with about 10 tables >with 5 - 25 indexes per table (10 - 30 columns per table). > >Users were complaining about performance... so I looked at some queries >that were taking waaaayyy too long to run (> 12 hours). One particular >query was taking about 11 hours to run... it involved 5 tables with >and/or/matches (query-tool generated). I captured the query and ran it >with "set optimization low" and it ran in 35 seconds. I encountered >similar results with other queries. > >It seems obvious that the optimizer is taking up all that time trying >to determine the most efficient way to get at the data (I'm still >fairly new to Informix ---- so if anyone has any other thoughts, please >let me know!!!!) > >I did some reading to try to determine what I could do about it. There >were some suggestions about adding WHERE conditions to force it to take >a particular index path... and this does work for certain queries. > >I can't tell my users to use "set optimization low" all the time since >the majority of queries are generated by 3rd party query tools. Also >it doesn't ALWAYS help, of course. I also can't expect them to add >bogus WHERE conditions or select their tables in a certain order... how >could they even know what would work best???? > >We are planning to purge out unneccessary indexes... but will this help >with queries containing > 4 tables????? > >I have to also be careful about tuning the ONCONFIG since I have 8 >other applications (>1000 users) on this server (which is a Data >Warehouse/Read Only server for the most part - 95%). > >ANY suggestions or thoughts would be VERY welcome!!!! >-- >Posted via Talkway - http://www.talkway.com >Surf Usenet at home, on the road, and by email -- always at Talkway. >

On Thu, 15 Apr 1999 02:34:29 GMT, "katherinec" <kmc3@navistar.com> wrote: >I "aquired" another Informix database recently. It seems that the >answer to past performance problems were to throw indexes at it. The >result is a database that is less than 2 G-bytes with about 10 tables >with 5 - 25 indexes per table (10 - 30 columns per table). > <snip> > >I have to also be careful about tuning the ONCONFIG since I have 8 >other applications (>1000 users) on this server (which is a Data >Warehouse/Read Only server for the most part - 95%). > Just a small suggestion - would it be possible to put this database (and any others like it) in a separate instance which could then be tuned differently to suit the sort of operations it has to cope with. Richard

Thanks for the response!!!!! A few people e-mailed me suggesting Update Statistics be run. We do run it on a weekly basis. Let me tell you about this... there is a particular example I have of restoring a similar database on the same server (using dbimport). I cut out the update statistics from the import script. Then, of course many queries were taking longer.... but some of them (the really long 10,000 second ones) began running in under a minute. I ran Update Statistics and the really long query times came back for those queries. I think I'm looking for a way to figure out how I can set up the indexes (maybe just delete many of them) so that the optimizer doesn't spend so much time analyzing the appropriate index path.... But I will take a closer look at our weekly Update Statistics Scripts. On Thu, 15 Apr 1999 09:44:36 +0100 "Neil Truby" <ntruby@netcomuk.co.uk> wrote: > Has an UPDATE STATISTICS been run on the database recently. If it's quite a > dynamic database, or the stats have not been updated recently, the optimiser > will be unable to take full advantage of appropriate indexes. > > Just a thought, as this is something that could make a dramatic difference > for little effort. > > Neil Truby > Londis Stores > Hampton Hill, UK > > katherinec wrote in message ... > >I "aquired" another Informix database recently. It seems that the > >answer to past performance problems were to throw indexes at it. The > >result is a database that is less than 2 G-bytes with about 10 tables > >with 5 - 25 indexes per table (10 - 30 columns per table). > > > >Users were complaining about performance... so I looked at some queries > >that were taking waaaayyy too long to run (> 12 hours). One particular > >query was taking about 11 hours to run... it involved 5 tables with > >and/or/matches (query-tool generated). I captured the query and ran it > >with "set optimization low" and it ran in 35 seconds. I encountered > >similar results with other queries. > > > >It seems obvious that the optimizer is taking up all that time trying > >to determine the most efficient way to get at the data (I'm still > >fairly new to Informix ---- so if anyone has any other thoughts, please > >let me know!!!!) > > > >I did some reading to try to determine what I could do about it. There > >were some suggestions about adding WHERE conditions to force it to take > >a particular index path... and this does work for certain queries. > > > >I can't tell my users to use "set optimization low" all the time since > >the majority of queries are generated by 3rd party query tools. Also > >it doesn't ALWAYS help, of course. I also can't expect them to add > >bogus WHERE conditions or select their tables in a certain order... how > >could they even know what would work best???? > > > >We are planning to purge out unneccessary indexes... but will this help > >with queries containing > 4 tables????? > > > >I have to also be careful about tuning the ONCONFIG since I have 8 > >other applications (>1000 users) on this server (which is a Data > >Warehouse/Read Only server for the most part - 95%). > > > >ANY suggestions or thoughts would be VERY welcome!!!! > >-- > >Posted via Talkway - http://www.talkway.com > >Surf Usenet at home, on the road, and by email -- always at Talkway. > > > > -- Posted via Talkway - http://www.talkway.com Surf Usenet at home, on the road, and by email -- always at Talkway.