Re: INFORMIX SE AND INDEXING

In article <CnFrrE.6Lq@boi.hp.com> harry@boi.hp.com () writes: > With Informix SE, over the course of the last few months, we have >been experiencing substantial performance degradation. One thing that >has been done is adding more indexes to tables. > What are the effects of adding indexes? How does this change the >performance? Is the order of index generation important? > It is the reporting aspect that has been hit the hardest. >Unfortunately, all of the indexes are used to aid in reporting and all >of the indexes are used. > Online is not an option. Any help would be appreciated. Do you drop and recreate your indices on a regular basis? I don't know about INFORMIX/SE but on Turbo the indices become unbalanced over time causing applications using them to chew up large amounts of CPU time. When the indices are dropped and recreated, they are balanced and the time to use them drops way down. Of course this all changes as you continue to insert indices. You will have to keep rebalancing on a regular basis. Another possible cause of performance degradation is if your data in the key tables becomes scattered across the disk. Periodic repacking of the actual table data will also help performance but is probably not nearly as large a factor as unbalanced indices. Hope this helps. ========================================================================== John Sutherland Principal Consultant Systems Performance Group 276 Eiwo Court, #102 phone: (519) 884-1710 #5235 Waterloo, Ontario (519) 886-8110 Canada N2K 2M8 e-mail: jsutherl@tron.waterloo.ncr.com