A developer asks:

We have a quite big ISM file. Once a year we archive three year's old data from this file, and delete this data from the original file. This, of course, reduces the speed of reading from that file. This is understandable. Then we take an empty index file, and rebuild indexes into this new index file. But this doesn't improve the speed of reading. Why?

Although it's not immediately obvious what the problem is here, perhaps reviewing the fundamentals of the ISAM structure will help us identify the issue(s) involved.

To start with, of course we have the two files to deal with, i.e. the index (IDX) and data (IDA). Reading the file by key (either sequentially or by explicit key lookup) requires accesses to both files. In the general case, assuming a balanced index, it will take a few disk accesses to read a record from the IDA (most to locate the key, then one more to read the data record). Assuming no cache, each of these accesses is going to require a track-to-track seek operation (say, 5ms), plus a half rotation (say, another 4ms). So we'd be looking at, say, 40ms per record, or about 25 per second. That kind of performance would be so bad as to make modern data processing intolerable, and the reason we typically experience performance hundreds or thousands of times better than that is the disk cache and buffering.

The point is: ISAM performance is almost entirely dependent on cache and buffering efficiency. So we need to analyze the structure of the file before and after those deletions to get a sense of how it affects cache/buffer efficiency.

You didn't say what kind of performance numbers you were getting, but it might be useful to determine a per-second figure, just to see how close you are getting to the raw non-cache performance. And also to clarify whether that is for random or sequential accesses. (I'm guessing you're talking about sequential, since typically that is the way one would scan through a file for a report, and that is typically where you would really notice differences in performance.)

Sequential IDX accesses are always going to be efficient, because each IDX block holds many keys, in key order, so instead of several individual block reads to locate each random key, a single block read will give us many individual keys. So instead of, say, 4 block reads per key access, we might have 1 block read per 25 key accesses (a hundred to one improvement).

That's always going to be the case for sequential key access, but the case of the freshly-built index is going to be better than the index after many deletions, because when building the IDX, the individual IDX blocks are going to be optimally filled, whereas after a bunch of deletions, they are going to be relatively empty. So after deleting, say, half the records in a file, your IDX blocks are going to be relatively half as filled, and thus half as efficient.

A related consideration is how big the IDX blocks are, relative to the key size. The traditional IDX block size was 512, because that was the unit of allocation and access for disks back when ISAM was developed. But now the fundamental allocation unit is likely to be 2048, and it almost certainly takes no more time to read 2048 bytes than 512 bytes, and thus increasing your IDX block size to 2048 will quadruple the number of keys per block (thus quadrupling the ratio of logical key access vs disk block reads) without much or any increase in the cost of each block read.

Still, the main factor in how fast you can scan the IDX/IDA file pair (assuming sequential key access) is going to be the organization of records in the IDA file.

The ideal case would be where you order of records in the IDA file matches the order of the corresponding keys. That actually is a worst case for the standard OS-level cache, but it's a best case for the typical disk hardware. (Not only do disks typically buffer at least a track at a time (nowadays that might be a MB or more), but even when we have to advance the head to the next track, a track-to-track seek is much faster than a random seek.) So instead of the worst case of maybe 4 physical disk accesses per record, we're going to hundreds of records in a single physical operation.

It's not clear how well reverse IDA order will work, but my guess is that it will be much less efficient than the forward order just described. (This is because the assumption of designers tends to favor forward access, because this is the most natural way for data to be written and therefore to be re-read.)

Random IDA order will probably be the worst, and quite terrible compared to the optimum case. You might start to get some OS-level cache hits (assuming the IDA record size is smaller than the cluster size, and that the overall file size doesn't overwhelm the overall cache size), but the disk hardware buffering efficiency is going to drop to almost zero. Not only are you not going to benefit much from the typical track-at-a-time buffering, but you're likely to have to move the heads for each logical read. So even though the IDX efficiency will still be high, instead of reading dozens or hundreds of IDA records per physical disk access, you might only get a couple of logical records per disk access. That will make a huge difference in performance.

So now we can consider how deleting a bunch of records from the file is going to affect the arrangement and thus the performance.

Deleting a record from the IDA doesn't change anything - it just adds that record into the "delete chain". Deleting a key from the IDX has a bit more effect in that it reduces the number of keys in the corresponding lowest-level IDX block, which reduces the number of logical record operations per disk read.

But when you delete records and then add new ones, the new ones use up the records in the deleted chain in last-deleted-first-allocated order. Quite likely this will result in the new IDA additions being in reverse order to the keys (although I'm assuming here that the typical key order matches the typical chronological order).

You say that after deleting the old records, you create a new IDX and rebuild it, but you don't say how you did that. If you used ISMDMP followed by ISMBLD, then the new file should be optimized for sequential access based on the key used to dump the records. But if you have a custom load program that reads from a copy of the IDA, or perhaps from the original IDA itself, then you won't be getting any optimization of the IDA in the process.

So I guess to really understand why it is slower after your delete/rebuild, we need to get a bit lower into the details, i.e. how you rebuilt file, and how you typically access it.

In my experience, aside from the somewhat speculative IDA sequence factors just discussed, by far the most important factor in ISAM performance is making sure that the structure of the IDX is optimized. With the original ISAM, you had no option - all IDXs used 3 levels of 512 byte blocks. That worked ok up to a certain size, but once you filled up the tree, it effectively toppled over, resulting in a potentially huge number of IDX reads required for each key access. Fixing or avoiding that problem will make a very significant different in performance (possibly an order of magnitude).

The way to determine if you have that problem is to use ISMDMP /V to display the verbose statistics, and look at the "Count (in use) by level:" figure, i.e.

What you want is for the first number in the Count-by-level list to be 1. Each number above 1 increases the number of physical disk accesses required for each key lookup, so in the above case, instead of 3 index reads, we'll typically need about 15 (assuming that on average, we find our spot in the top level half way through the 26 blocks, which by the way, are almost guaranteed to be not physically near each other).

The way to fix that problem is to use the ISMBLD /A switch (to auto-configure the levels and block size), or to explicitly increase the block size and or number of levels using the /B:#### and /L:# switches.

That doesn't necessarily have any connection to why performance would suffer after the deletions and rebuild, but whenever ISAM performance is a consideration, this is the first thing you should look at.