Re: Optimizer taking too long to determine query path

One other consideration. With 7.3 we introduced the optimizer hints which can be used to elimiate various indexes from consideration by the optimizer. katherinec wrote: > 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.

Thanks to you and everyone else who has been providing feedback. A few of you suggested that I try update statistics low drop distributions. I did this and the queries that were taking >50000 seconds are now executing in less than 100 seconds!!!! I still think I have a way to go... I am meeting with the users tomorrow to determine which indexes are no longer necessary and to get a better idea of the kinds of queries that are likely. Also thrown into this mix is there is blob and varchar data in these tables. So I will... - get rid of unnecessary indexes to eliminate some overhead - further evaluate where I should run update statistics low, med, high (medium and high does negatively impact some performance... I need to figure out why) and evaluate when I should keep or drop distributions and set up a NEW update statistics script to be run once a week. Thanks again for all of your help!!!! Surf Usenet at home or on the road -- always at Talkway. http://www.talkway.com