Re: Informix Optimiser: Should I use it?

Fire your software supplier. They basically don't have a clue. One of the main points about using a relational database is to hide this level of complexity from users and developers. You tell the engine what to do, it figures out how to do it in the most efficient manner. Five years ago you might, in some odd circumstances, have had to resort to this kind of tweak as the optimisers were not that smart. Today if the database doesn't perform adequately then the normal reason is that either the database is not tuned correctly, the physical database design hasn't been done properly, the box its running on isn't big enough or the developer has designed the queries badly. It is very rare today to find that the optimiser is choosing the wrong path when everything else is correct. That doesn't mean that the developer doesn't sometimes, but more and more rarely with each new Informix release, have to help it by hinting how it should do the query. There are various ways the developer can do that. But bypass it completely! It basically means that your suppliers had to charge you more for their software than they should have done. This is because it cost them more to develop as the developers had to spend time doing what the optimiser should have been doing for them. In addition, you are trapped to the performance capability of their design. This is because the engines will always use the same access method. If the optimiser had been given the job then, as you upgraded the database engine, you would have seen performance benefits provided by the optimiser making better decisions. This occurs automatically without any need for you to purchase upgraded software from your supplier. One last point. You now cannot buy a report writer so that you can write your own reports from the database. You have to pay your software supplier to write each report you need. Without the optimiser having decent statistics it cannot do it's job for any software accessing the database. Report writers will run like snails. Only your software supplier will be able to make a report perform adequately. Nice scam don't you think! Hope this helps. Cheers - Jim On Mar 7, 9:34pm, Peter Wotherspoon wrote: > Subject: Informix Optimiser: Should I use it? > This may seem like a strange question, but our software supplier > has told us that we must on no account update the database > statistics, as it will (a) make queries run more slowly, and > (b) make the rows return in the wrong order - they rely upon > the implied order of rows returned by their chosen index rather than > using "order by". > > They force Informix to use their chosen index by declaring a > dummy data column, with a standard value (always "0"), and putting > this at the start of the "where..." clause (eg. "where index_dummy1 > = "0" ...). The reason we mustn't run "update statistics" is that > the optimiser might decide to use a different index if it's allowed > to know how many rows are in each table. > > This means that they are effectively bypassing the Informix optimiser > by specifying their own access paths. > > An Informix consultant who has spoken to one of my colleagues (now > sadly departed to work for Sequent) does not seem overly impressed > by the software supplier's approach. I am interested to know whether > ANYONE else thinks it's a good idea. > Peter Wotherspoon >-- End of excerpt from Peter Wotherspoon -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!