Re: Query Optimising Problem !!

Optimizing a query can be very quick once you type set explain on and then run your query. The output will tell you how the optimizer has decided to process your request: it will show you what indexes it uses,what filters and when it is doing a sequential read. It can be surprising to see the path that the optimizer chooses. You may begin to realize how much more you know about your data than the optimizer does. We have been using Informix in the same application for four years. As our tables have grown we have seen the optimizer change the path it wishes to take and we have come up with what we hope are techniques that will stabilize performance. First, never rely completely on the optimizer. Index the fields that you are most likely to search on. Then, make use of temp tables. Make sure joins are done on indexed fields whenever possible, and again, pre-select "key" records from one large table into a small temp table and then do further joins. Only you know what your data really looks like. Second, subqueries will not necessarily be processed first, so do not rely on them to set up your small subtables that will make your queries oh so efficient. Use temp tables to make subsets to drive your searches. They are more reliable. Again, only you know what your data truly looks like. Third, always update statistics. Fourth, check the npused column in your systables table. If it is negative, then you have the bug that slowed so many of our queries. The number goes negative if the table has a large number of deletes done on it. The cure is to unload it, drop it, create it again and reload it. The condition will reoccur. The bug is fixed in 5.03. If you do the temp table thing above then you won't be bothered by it. Otherwise, the joins on the table are incorrectly evaluated by the optimizer and the performance degradation is frightening!! First, never rely on the optimizer. If your data is added by date, and will be most frequently accessed by date, then index the date. Then, in an application that will look at your data one day at a time, pull the days data into a temp table and then select again on whatever additional criteria that you have. When your data set has 1000 days of data with 1000 records a day, then you will pull only 1000 records, and look through those. This is an example of how you have to be the optimizer.