Re: Ref: Performance

Cheryl, I tried to e-mail this reply to you, but the mail bounced. Maybe the information would be useful to others, anyway..... ---------------------------------------------------------------------- In article <2skrp2$oog@emory.mathcs.emory.edu> you wrote: : 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? : 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? : cheryl : cheryl@redriverad-emh1.army.mil : 903-334-3518 Cheryl, You might find some of this e-mail that I wrote to my developers of some use: We seem to be coming down to asking the question of how much it costs us to add two fields to an existing index when it comes to inserting, deleting, or updating rows in tables that have the index. First, the cost of updating an index relates directly to the number of disk accesses necessary to get to the affected leaf pages of the B+tree index. All index accesses go first to a root node, which is almost always in shared memory, so it costs nothing. Look at the indexes, both old and new: Old: doctype smallint=2 bytes status char(1) =1 byte TOTAL 3 bytes New: doctype smallint =2 bytes status char(1) =1 byte batch integer =4 bytes sequence integer =4 bytes TOTAL 11 bytes Our indexes are dropped and recreated every night as part of the EOD process, so each index will be relatively "fresh", meaning that they will remain fairly well packed into their respective pages. This means that there will be relatively few "holes" in the B+tree. Each index entry approximates (disregarding index compression and empty nodes in the index) the width of the indexed columns plus 4 (for the rowid). Thus the old index has a 7 byte key, the new index has a 15 byte key. Each page is 2048 bytes, so here's how the indexes fit on a page: Old 2048 bytes/page divided by 7 = 292 entries per page New 2048 bytes/page divided by 15 = 136 entries per page Thus, when the root page has x entries per page and each leaf page has x entries per page, you can accomodate x^2 rows in a two-level index and x^3 rows in a three-level index. This means that the index levels go like this: 1-level index 2-level index 3-level index (one access) (2 accesses) (3 accesses) OLD 1-292 rows 293-85,264 rows 85,265 - 24,897,088 rows NEW 1-136 rows 137-18,496 rows 18,497 - 2,515,456 rows As long as our stage table is less than about 2 1/2 million rows, the cost of updating the new index will be the same as the cost of updating the old index. It will cost three disk accesses, one of which will always be in shared memory). There is a "flat spot" of between about 20,000 and 85,000 rows in which the old index will have a two-level index and the new index will have a three-level index. Here, there will be an additional page read necessary during updates (and queries). There is no additional additional cost to update an index of 4 columns as opposed to 2 columns as long as no additional disk accesses are required. Where the cost of indexes really increases is when you ADD ADDITIONAL indexes, not when you add columns to an existing index. You might also want to look at the 5.02 (or .01 or .00) _Guide to SQL, Tutorial_ pps 10-20 thru 10-22 for a discussion of the cost of indexes. What you may be seeing is the HIGH cost of inserting or updating an index with few distinct values....look at 10-22 for this. I'd be hesitant about having too many indexes. Lots of overhead. Besides, you can probably make do with several multipurpose indexes. If you really ***NEED*** all those indexes, you probably need to split up your tables into more normalized tables. Good luck, Joe "Bubba" Lumbley -- =========================================================================== jlumbley@netcom.com (Joe Lumbley) BancTec Service Corportation 214-450-9896 Dallas, Texas Watch for my _INFORMIX DBA SURVIVAL GUIDE_ in Fall '94 from Prentice Hall! =========================================================================== -- =========================================================================== jlumbley@netcom.com (Joe Lumbley) BancTec Service Corportation 214-450-9896 Dallas, Texas Watch for my _INFORMIX DBA SURVIVAL GUIDE_ in Fall '94 from Prentice Hall! ===========================================================================