Re: Optimizer taking too long to determine query path

The time that a particular query takes to be optimized is almost totally dependent on the number of joins and indexes on the joined tables within the query. It might be that if you proceeded the query with "set optimization low" that you would see better performance. This is assuming that the delay is with the optimization phase and not the execution phase. M.Pruet 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.