Re: Extents and query performance

> Recently I was adjusting a performance test, trying to keep it in a > reasonable amount of time (for a test) and at a time one of the querys > started to take much less time than before. The people that were doing the > test told me that the only change made was in the extent size of the table > involved in the query. Before it had a first extent size several times the > size of the table. After the adjustment, it was set with a first extent very > close to the size of the table. > > Now my question: Could this change in the extent size be the cause for > the performance increase, or would it be that with a smaller extent the > table was created near the other tables involved in the test, causing the > seek times to be smaller? > > Comments please, and thanks. Yes. It could be that the smaller extent size change greatly affected the layout of the table on the disk, including its proximity to other tables in the test. Without greater detail I can't really get specific. In general: You could have had a table that had many extents which were interleaved all over the disk and which had rows that were fragmented all over the tablespace, causing excessive seek times. One way to fix this is to alter the index to cluster (perhaps changing the first extent size, too). After this operation your rows would be physically arranged on the disk in the order of the key index, and potentially all in contiguous space, and you could see a pretty hefty speed increase (through more efficient I/O operation). The only reason for a speed increase through a *reduction* in first extent size might be in an application where there was a big hole in the data (through a large delete, for example) and the engine had to read through pages with no data. We normally see performance increases via an *increase* in the first extent size, as all rows come to occupy contiguous space. If the table formerly had >8 extents, and <8 extents after the rebuild, then you get a very marginal increase in memory efficiency, as the structure which holds the list of extents can only hold 8 extent addresses. While we're on the subject of extent handling, changing the FIRST extent size may not even do any good. (I was told that at the user conference by one of the speakers.) If the table loads into a series of adjacent extents then the engine treats them like one extent. That is, it simply concatenates all the extents into one larger one. Raising the size of the NEXT extent will force the engine to grab a larger extent after the first one is filled. That's about all the performance increase you can expect from a table rebuild. (You can change allocated tablespace to available dbspace, but there is no performance benefit directly from that.) You can learn more about extents and I/O in the Informix "Guide to SQL Tutorial", especially in the December 1991 edition, which has a much better explanation of extents than the December 1994 version. Hope this helps, __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|