Performance problems in Informix 7.11 -- Thank you

Thanks a lot to all of you who replied to my email "Performance Problems in Informix 7.11". I am summarizing your suggestions (and some of my own, which I learnt during the course of the problem) for the benefit of others who might be interested: 1. Run your SQLs on a copy of live data (to simulate volume) with SET EXPLAIN ON and try to reduce sequential scans as far as possible by adding indexes and changing the SQL statement. 2. Try to make your index highly unique rather than highly duplicate. This is likely to convince the optimizer to use your index. 3. Try to reduce creating implicit temp tables for ORDER BY and GROUP BY by supplying the index on whatever the ORDER BY or GROUP BY is. 4. If the range of values requested in the WHERE condition is very large so as to span almost the entire table, the optimizer will do a sequential scan regardless of indexes. 5. UPDATE STATISTICS regularly with different resolutions depending on the table size and other factors such as the number of hits on the table. 6. EXTENT SIZES should be updated on a regular basis to make most of the data fall within the initial extent. 7. Indexes that dont buy us anything in terms of SELECT performance should be dropped because they slow down performance on INSERT and UPDATE. 8. PREPARE SQLs that need to be called repeatedly. 9. Use LOCK MODE ROW. 10. For 5.x to 7.x conversion, control the priority of the CPU VP process by using rtprio and the onconfig parameter NOAGE. 11. Increase the number of connexions in the onconfig parameter NETTYPE as well as the poll threads, to reflect the number of users who would actually be using the system. 12. Assign a large value of SHMVIRTSIZE so that the engine does not allocate shared memory dynamically. In HP-UX this would lead to a large performance degradation after 2 segments. Hope this helps Sujit Pal DBA, Computer Sciences Corp.