Re: Ref: Performance

->Date: Thu, 2 Jun 94 9:09:25 CDT ->From: "cheryl@redriverad-emh1.army.mil" <cheryl@RRAD05.ARMY.MIL> ->To: informix-list@RMY.EMORY.EDU ->Cc: cheryl@redriverad-emh1.army.mil ->Subject: Ref: Performance -> ->I added another index to a table in my database (the 6th one) [1] ->and my performance took a turn for the worse. -> ->Where is the trade off, when are there too many indexes? How [2] ->do most folks handle the need for indexes? Do they create [3] ->temp tables for ace reports and have only 2 or 3 main indexes? -> ->We are doing very little 4GL, mostly .ace reports and perform screens ->with ESQL behind the the perform srcreen. Is 4GL faster? [4] ->(I know, it depends on the data, design and hardware...), but ->is it possible to get the performance using temp tables instead ->of indexes? -> ->cheryl ->cheryl@redriverad-emh1.army.mil ->903-334-3518 -> [1] This DOES seem like too many indexes for one table, but needs vary. Be sure to avoid the newbie mistake that I made a couple of times before I understood indexes: I.e., don't have both of the following: CREATE INDEX two_part ON some_table ( col1, col2 ); CREATE INDEX three_part ON some_table ( col1, col2, col3 ); Informix can use the LEADING columns of a composite key index as tho' they were in an index by themselves. Therefore, you get no benefit from having index two_part, but you pay the cost of maintaining it. [2] Trade-off seems to be between 5 and 6 indexes. :-) Sorry, couldn't resist. [3] More seriously, consider how often you are using the indexes. If a certain index is only used for weekly or monthly reports, it might be advisable to create the index before running the report, remove the index after, and not have the index most of the time. Lacking info to the contrary, I would expect it to take less time to build an index instead of a temp table, unless the temp table is a LOT smaller then the base table. [4] I don't use ACE, I do mostly 4GL and a little PERFORM. Therefore, I can't say for certain whether 4GL is faster than ACE. However, 4GL is definitely more flexible. (This is one reason I use only 4GL for reporting.) To continue my idea from [3], I don't know whether you can use the CREATE INDEX command within ACE, but you can do so within 4GL. Thus, you could build the index create and drop steps into the report program, eliminating the need for manual intervention. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\