Re: Query Optimising Problem !!

In article <3i16po$c1s@raffles.technet.sg>, sgs@technet.sg (Alan Chen) says: > >I have an application running on Online 5.01. Recently the performance >has been degraded, I have checked with Informix Support & they verified the >database is actually quite well tuned. So, they propose us to do some >study on the query execution plan to optimise the transaction. > >I have never done any query optimisation practise in Informix Online >before, can anyone provide me some pointers ?? Any good reference book ?? >Any idea will be appreciated. > I havn't used OnLine but have considerable experience in SE database, and many of the problems are similar. The previous replies mention 'SET EXPLAIN ON', and I personally very keen on the suggestion of providing a mechanism to allow you to dynamically turn it on according to a command line argument or shell variable - there is a function 'fgl_getenv' which you can use e.g.: l_var = fgl_getenv( 'SW_EXPLAIN' ) if l_var is not null then set explain on end if I consider this more useful than extracting source into 'dbaccess' as the statements may vary at run time according to screen input etc. and the values actually used will be seen in this manner. I would also mention the Informix FAQ document which makes some valid points about improving performance with Indexes, and finally add the following thoughts: o The 'cost' in the results of 'set explain on' can be bizarre at times, but basically (as mentioned) you want to eliminate scans; o The database may not be the source of your problems. Other causes can be 'oddball' users running horrendous queries, a change in your user population, bottlenecks in comms software, memory shortage - and so on. Good Luck with sorting your problem Sally Woolrich