Re: Performance

Sorry to waste bandwidth, but this mail keeps bouncing when I send it directly. cheryl@redriverad-emh1.army.mil writes: -> ->I have a performance problem...and I hope it will not fall into ->the bad category. -> ->I added another index to a table in my database (the 6th one) ->and my performance took a turn for the worse. -> ->Where is the trade off, when are there too many indexes? How ->do most folks handle the need for indexes? Do they create ->temp tables for ace reports and have only 2 or 3 main indexes? The major trade off is disk space. So long as you have the space for the index and you aren't indexing everything under the sun, you should be ok. The first BIG question to ask is how unique is the new index you created? When we first came up on Informix with all our old data, our performance was pitiful. It turned out to be the fault of badly designed indexes (I didn't know any better at the time). We had some indexes which were based upon columns with many duplicate values. This is actually relatively easy to fix. Drop your new index, and create a composite index: <your slow index> + <a unique (or pretty unique) value> Indexes with lots of duplicate values are likely to be an enormous culprit. ->We are doing very little 4GL, mostly .ace reports and perform screens ->with ESQL behind the the perform srcreen. Is 4GL faster? ->(I know, it depends on the data, design and hardware...), but ->is it possible to get the performance using temp tables instead ->of indexes? Yes, temp tables will make you faster. A good rule of thumb is to narrow down your search as much as possible to start using the fewest number of tables possible in each search. Use temp tables (with no log!) to store intermediate results. Good query design can help a lot... but you still may need to add an index. I hope this helps some. Let me know if it does. If it doesn't, I would be happy to look at some more detailed info... there is usually a solution to these problems! Regards, - Cathy -------------------------------------------------------------------------------- Cathy Kipp e-mail: ckipp@vth1.vth.colostate.edu Phone: (303) 491-1294 Colorado State University Veterinary Teaching Hospital Fax: (303) 491-1205 Coming in late summer, from Prentice Hall, my new book: Programming INFORMIX SQL/4GL: A Step-by-Step Approach (ISBN: 0-13-149394-9)