What to expect from ChimneySweep® when your database is working properly now ...
When your database is working properly, the primary goal of ChimneySweep® is to help you keep it that way. There are several important points that you should consider.
Anticipate problems: scan tables regularly.
You don't want to discover that your database has a problem only when users start calling you. Instead, you want to regularly scan the database for problems. It's a good idea to do this with a job that combines a table-scan, index optimization, and the creation of an on-disk backup image.
When to optimize indexes.
An index becomes less-optimal the more intensely it is used. Tables which experience many inserts and deletes, especially when those insertions are at random locations within the table, will slow down fairly quickly; sometimes quite precipitously. These tables need to be optimized often, whereas tables that are mostly unchanging will need little index maintenance.
When to compress (“pack”) tables.
Unlike some other database systems, Paradox databases “recycle” the space occupied by deleted records. But they do so only in “blocks” of records. Depending on the usage pattern of the table, space can gradually become filled with partially-full blocks. Packing the table will pack the data so that a much smaller number of nearly-full blocks are used.
It is not mandatory to pack a table, nor is it necessarily a good thing to do too often. As the newly-packed table continues to be used, a great many of those blocks will be “split” to make room for new records, and soon you will have a great many blocks that are about 50% full. Again depending on the usage-pattern experienced by your tables, the resulting situation may actually be worse-performing than before. Table size tends to more-or-less stabilize over time, and performance may be improved more by optimizing the indexes than by packing the table.
Cleaning-up lock files; “ghost locks”
Paradox tables use “lock files” to record information about the record-locks and other locks that have been placed. These files can become large over time, and it is a good idea to use ChimneySweep to delete them. (If anyone is using the lock-file at the time, the lock-file will of course not be deleted.) This is also a convenient remedy for the occasional problem of “ghost locks,” where some type of lock is not removed correctly. Removing the lock-file, if successful, will by-definition remove all the locks, “ghost” or otherwise.