EasyTable: Single-File Embedded Database,
a BDE replacement for Delphi and C++Builder
EasyTable is well optimized and balanced database system. We spent a lot of time to make it one of the most advanced and fast BDE alternatives. Moreover it provides methods of tuning and optimization for some special tasks.
This chapter is divided into the following topics:
- optimizing database file size;
- increasing searching and filtering speed;
- improving an insert / edit / delete operations performance;
- minimizing memory usage.
Optimizing database file size.
One of EasyTable advantages in comparison with other database systems is a very compact database file. Index file requires only 4 bytes per field to store an index value for the single record. BLOB field values can be optionally compressed (See BLOB fields compression topic for more details)
EasyTable allows you to optimize the database file size using DatabaseFileMode property of TEasyDatabase component. There are three available values for this property: dfmCompact, dfmNormal (default) and dfmLarge. Choose a dfmCompact value to get most compact database file. If you plan to use large databases (> 10 Mb), choose dfmLarge mode. In other cases dfmNormal probably will be the best choice.
The EasyTable database file structure is similar to the OS file system (FAT, NTFS) Thus, deleting of the data will not immediately decrease database file size. Most likely some pages inside the database file will be marked as empty. So, if you need to compact the database file, use CompactDatabase method of TEasyDatabase component. But if you want to prepare the most compact database (for writing to the CD, for example), you should call TEasyDatabase.RepairDatabase before CompactDatabase.
Another way of making the database file smaller is to set TEasyTable.BLOBBlockSize before creating tables with BLOB fields. The smaller value will provide more compact form for BLOB values, but lower read / write performance. The minimum BLOB block size is 10 bytes.
Increasing searching and filtering speed.
To increase searching and filtering speed, you should either manually create necessary indexes on search fields or use auto-indexes (Set TEasyTable.AutoIndexes property value to True before table creation). The index can be created by calling AddIndex method of TEasyTable component as well as by using CREATE INDEX SQL statement. The EasyTable engine uses only ascending indexes to speed up search / filter operations. If you are going to perform case-insensitive searches you should create case-insensitive index (if you do not use auto-indexes).
Note that each additional index causes slowing down insert / update / delete record operations.
Improving an insert / edit / delete operations performance
The most common way to increase the speed of the table modifications is to avoid permanent index updating. You can create table without indexes (set AutoIndexes property to False). If you have table with data and some indexes you can drop them (DeleteIndex method of TEasyTable or DROP INDEX SQL statement), modify table and create them again. This approach is extremely useful on large databases (> 100,000 records).
Another point is the speed of assigning large size values to BLOB fields. This process can be speed up by increasing Blob Block size (to 1,000-10,000 bytes) before table creating
or on restructuring. This however will increase the database size.
Minimizing memory usage
EasyTable uses read / write buffering to achieve the highest performance. By default EasyTable automatically configures the size of records cache. However you can force EasyTable to minimize amount of RAM used for records cache by setting the CacheEnabled property to False before opening a table or executing a query. This however can decrease overall performance.
|