Icon Optimizing Tables

Introduction
Optimizing tables is accomplished through the OptimizeTable method of the TDBISAMTable component. The properties used by the OptimizeTable method include the DatabaseName, TableName, and Exists properties. Optimizing a table will physically re-order a table's records based upon a specific index in order to improve read-ahead performance and will also physically remove any empty space from a table. By default the index used for the re-ordering of the table records is the primary index.

Optimizing a Table
To optimize a table, you must specify the DatabaseName and TableName properties of the TDBISAMTable component and then call the OptimizeTable method. The table component must be closed and the Active property must be False. It is usually good practice to also examine the Exists property of the TDBISAMTable component first to make sure that you don't attempt to upgrade a non-existent table. If you do attempt to upgrade a non-existent table an EDBISAMEngineError exception will be raised. The error code given when a table upgrade fails due to the table not existing is 11010 and is defined as DBISAM_OSENOENT in the dbisamcn unit (Delphi) or dbisamcn header file (C++). DBISAM will attempt to open the table exclusively before optimizing the table. If another session has the table open then an EDBISAMEngineError exception will be raised when this method is called. The error code given when optimizing a table fails due to the table being open by another session is 11013 and is defined as DBISAM_OSEACCES in the dbisamcn unit (Delphi) or dbisamcn header file (C++).

The following example shows how to optimize the "customer" table using the OptimizeTable method:

begin
   with MyTable do
      begin
      DatabaseName:='d:\temp';
      TableName:='customer';
      if Exists then
         OptimizeTable;
      end;
end;

In addition to using the TDBISAMTable OptimizeTable method for optimizing tables, DBISAM also allows the use of the OPTIMIZE TABLE SQL statement.

Tracking the Optimize Progress
To take care of tracking the progress of the optimization we have provided the TDBISAMTable and TDBISAMQuery OnOptimizeProgress events.

Backup Files
By default, DBISAM will make backups of a table's physical files before optimizing the table. You can turn this off via the second parameter to the OptimizeTable method. Each physical file will have the same root table name but with a different extension. These extensions are as follows:

Original ExtensionBackup Extension
.dat (data).dbk
.idx (indexes).ibk
.blb (BLOBs).bbk

To restore these files in case of a mistake, simply rename them to the proper extension or copy them to the original file names. Also, these backup files will get overwritten without warning for each optimization that occurs on the table. If you need the backup files for future use it's best to copy them to a separate directory where they will be safe.

The file extensions described above are the default extensions and can be changed. Please see the DBISAM Architecture and Customizing the Engine topics for more information.
Image