Re: INFORMIX SE AND INDEXING

> 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. In addition to Alan's excellent suggestions, be sure you update statistics regularly. The optimizer relies on these statistics to formulate the query plan for each query. Stale statistics can degrade performance gradually such that it isn't necessarily discernible from day to day, but can be substantial when measured over a period of time. If you regularly schedule your reporting in batch, you might put a dbaccess step which updates statistics into your schedule just before the report processes. ___ ___ Senior Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 dberg@informix.com The opinions expressed are my own.