Thunderstone Support Forums

Happy New Year all. I have a question. We have some tables that are very dynamic and probably get fragmented over time. I'd like to know how much performance would improve by regular packing (and reindexing too if that's desirable).

Do you have any way to estimate the fragmentation or empty space in a table and any guidelines about when packing is worth doing (if ever). It would take a fair amount of time to copy these tables and rebuild the indices but I'd like to do it if it would significantly improve performance. Even if it doesn't impact performance, is it good to do just as a hygienic measure?

I thought about doing some experiments on one of our larger tables to see how much faster queries would run -- but then I thought maybe I could just ask you instead.

Tom

With dynamically changing tables it's usually not worth the effort to re-pack them (eg. via copying), as KDBF will pick the best-fit free block for each insert.

However, if something has changed significantly over the long run, a re-copy might have benefits. For example, if the average inserted-row size has decreased significantly and consistently (an order of magnitude say), or the total table size (all inserted rows) has decreased a lot relative to file (.tbl) size (a lot of data was deleted that will not be re-inserted with new data), then a re-copy might condense the table enough to remain condensed (and benefit seek time) for a while to come.

kdbfchk does not report free space per se, but a quick estimate can be made by looking at the first bar graph it produces ("Reading free tree and list"): the percentage-distance that the hash marks (###) reach is the rough percentage of free blocks in the table. This does not include slack space within allocated blocks however.