Re: Optimizer taking too long to determine query path

I'm aware of some bugs involving the optimizer and distributions. You might try doing UPDATE STATISTICS LOW with DROP DISTRIBUTIONS, and don't run HIGH or MEDIUM. Otherwise, try running UPDATE STATS according to the recommendations in the Performance Guide. I wrote an ESQL/C program which does this, and submitted it to the IIUG, but I'm not sure if it's been released to the public. katherinec <kmc3@navistar.com> wrote in message news:iQmR2.2937$fH2.1251@c01read04.service.talkway.com... > 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. >

Thomas J. Girsch wrote: > > I'm aware of some bugs involving the optimizer and distributions. You might > try doing UPDATE STATISTICS LOW with DROP DISTRIBUTIONS, and don't run HIGH > or MEDIUM. > > Otherwise, try running UPDATE STATS according to the recommendations in the > Performance Guide. I wrote an ESQL/C program which does this, and submitted > it to the IIUG, but I'm not sure if it's been released to the public. It's easy enough to go to the Repository and find out. What did you call it. BTW my program dostats.ec does just this also. The latest is included in the package I submitted as utils2_ak (a very old version is also in utils_ak) which is indeed available from the IIUG Software Repository in the Database Adminstration grouping. Art S. Kagel Wondering if the fact that it took me six tries to spell my own name correctly means I'm getting old!