Re: Indexes: Attached or Detached?

Some time back, I ran a few test cases, with the following results: --- begin included document excerpt -------------- A series of tests was run to investigate certain questions about space allocation for Informix indexes. The general method of the test was to define a table in a dbspace, define a unique index on a serial key within that table, and load the table using the SQL LOAD FROM statement. This was done using the Unix time command so that relative performance of various options could be determined. After each test, the Informix oncheck command was run to print the structure of the resulting index pages. The data used to load the table included 38,313 rows of varying lengths. The rowsize was 769 bytes, but many varchar columns resulted in an average row length of less than 360 bytes. A second series of test was then run to check similar questions about the SQL CREATE UNIQUE INDEX statement. In these tests, the table created in the firsttest was used to create a unique index on the same serial key but in descending order. This was done as it was the easiest way to guarantee uniqueness for the second index. Again, the Unix time command was used. To ensure that the tests measured comparable work, the instance was bounced between each test. The test machine was an HP K200, running HP-UX 10.20. Informix was 7.14.UD1. Case # Description Time ====== =================================== ======== 1 Load table, data in key order, real 4m8.41s attached unique index user 0m29.33s sys 0m1.44s 2 Load table, data out of key order, real 4m15.03s attached unique index user 0m27.76s sys 0m1.47s 3 Load table, data in key order, real 1m37.20s detached unique index user 0m29.56s sys 0m1.96s 4 Load table, data not in key order, real 1m25.69s detached unique index user 0m27.79s sys 0m1.71s 5 Create attached unique index, real 0m4.52s fillfactor 100 user 0m0.03s sys 0m0.04s 6 Create detached unique index, real 0m11.34s fillfactor 100 user 0m0.03s sys 0m0.05s 7 Create attached unique index, real 0m9.01s fillfactor 20 user 0m0.03s sys 0m0.04s 8 Create detached unique index, real 0m17.80s fillfactor 20 user 0m0.03s sys 0m0.05s --- end included document excerpt -------------- In looking over these results now, I realize that I didn't run a test of loading the data with no indexes, which would be an interesting baseline. The original purpose of this test was really to see the impact of some of these cases on index structure, rather than performance. That's why I was dumping the pages with oncheck after each run. That is also why, in these tests, the index and table were both created in the same one-chunk dbspace. Yes, the index is detached from the table, but it resides in the same space. It has its own partn and is shown in sysfragments. The fact that they were in the same chunk makes the results even more surprising. As you can see, the detached indexes were significantly faster when loading data. I have not had time to perform a similar set of tests for updates or deletes, and I admit that the performance of LOAD...FROM may not accurately reflect the performance of INSERT statements, so you may want to construct a similar benchmark of your own. That said, I agree with Sujit Pal about spreading I/O across multiple disks (the above contrived tests notwithstanding), and have used the trick he mentions to see how much activity a particular index has, though I used onstat -t instead of sysptprof. > I wanted to pose a question to the group: Do you prefer to use attached > indexes or detached indexes? If you have a preference, why do you have that > preference. Have you noticed any key differences/benefits in one vs. the > other? > > Thanks in advance, > > - Tom Girsch > tgirsch@iname.com Mark Collins mcollins@us.dhl.com "Be what you would seem to be"--or if you'd like it put more simply--"Never imagine yourself not to be otherwise than what it might appear to others that what you were or might have been was not otherwise than what you had been would have appeared to them to be otherwise."' - Lewis Carroll, Alice in Wonderland