Re: Performance hit with large number of rows and compound index

> A client of ours has standard Informix 5.02 installed as part of a turn-key > system from a third party, and is experiencing a bad performance hit > on a table with a compound index, which has grown to over 30,000 rows. > > They have heard a rumour that this is a known problem. Can anyone shed any > light? > --------------------------------------------------------- > Chris Palmer > Deloitte Touche Tohmatsu, Auckland, New Zealand > c.palmer@dtt.co.nz This is most unlikely to be a known problem I don't know of any known problems on compound indexes. With proper database administration, people are using Standard engine with much, much larger tables and compound indexes. Proper database administration is the key. This will depend on the design of the database, but all B tree indexes become skewed over time and need to be rebuilt. This is best done by doing "alter index indx_nm to cluster". This will not only rebuild this index it will rebuild all the other indexes on the table and put the table in the same physical order as the named index. You must have enough disk space to have two copies of the .dat and .idx files existing at the same time. This job needs to be done on a regular basis depending on the update frequency of the table. I would recommend at least once every three months, on highly active tables perhaps more often. This may not be the only problem. When was the last time they ran "update statistics" on the database. It is quite possible that the cause of the problem is that the select statement is not using the index at all and is instead using a sequential scan or a different inappropriate index. Optimisation of selects is done automatically by the database based on statistics held. These statistics are updated by running "update statistics". Bad statistics will produce extremely bad access paths and very slow performance. I will lay a pretty heavy bet that they haven't done either of these things since they installed the system. If they haven't try doing update statistics first. Cheers - Jim My opinions are my own. They may vary with time but they remain MINE! ---------------------------------------------------------------------- Name: Jim Gordon Company: DHL Systems Inc, Burlingame, CA, USA ----------------------------------------------------------------------