Accuracer: The First And Only BDE Alternative
Client-Server Single-File Embedded Database for Delphi / C++Builder / Kylix

BLOB and Varchar fields

Previous  Top  Next


Accuracer supports string fields of variable length - varchar SQL field type or ftString field type. Varchar fields allows to get more compact database file, as they stores only actually number of characters for each field of each record, while fixed length character fields always stores maximum number of characters. Aslo varchar fields like a BLOB fields can be optionally compressed.
We will use SQL terminology for field types naming to avoid misunderstanding.
Char fields is a fixed length character fields, while Varchar is a variable length character fields.

BLOB and Varchar fields compression is transparent, so you can easily use it if you store large amounts of data.


You can specify a compression settings for BLOB or Varchar fields:
1) Using ACRManager utility
2) Using AdvFieldDefs property of TACRTable - see Reference Guide, TACRAdvFieldDef class.
3) Using SQL script for creating tables - see
CREATE TABLE statement topic

How to choose a compression settings

First you should choose one of three compression algorithms: ZLIB, BZIP or PPM. ZLIB is the fastest, but provides lower compression rate. However it is recommended when you need to store short data (~ 1 Kb or less). BZIP is a little slower, but usually provides better compression than ZLIB. Both of these algorithms decompresses data (read operations) much faster than compresses data (write operations). The speed difference may be up to 10 times.
PPM is a much slower algorithm, providing best compression rate on most types of data. PPM decompresses data a bit slower than compresses it. This algorithm is recommended for large amounts of data (100 Kb or more).

After that you should choose a proper compression mode - integer value from 1 (minimum compression rate and maximum speed) to 9 (maximum compression rate and slower speed).

BLOB block size parameter is used only for BLOB (or Memo) fields, but not for Varchar fields. This is a size of memory buffer required for streaming compression. Default value is 100 Kb. If you set smaller value the memory buffer will be smaller, but it can lead to lower compression rate and speed. Larger value will provide better compression rate (especially for PPM).

Note:      Memory usage is determined by compression algoirhm and mode. Large mode require large amount of memory in BZIP and PPM. PPM requires from 2 Mb to 100 Mb of RAM to operate, BZIP requires from 100 Kb to 900 Kb of RAM and ZLIB requires up to 256 Kb of RAM. If you use BLOB (or Memo) fields Accuracer allocates a memory buffer which size is specified in BLOB block size parameter (100 Kb by default). Varchar fields does not allocates this buffer.
Note:      If you specify a compression algorithm None the compression mode value is ignored, while BLOB block size is still active for BLOB fields.

How to choose between Varchar, Char, Memo and BLOB field types

If you need to store binary values you should use BLOB fields.
As for text data it generally depends on the operations you need to perform against this data and on size of the data.
If need
any kind of searching or filters on this text you should use either Varchar or Char field types. Search in Memo fields is not supported (it can be done only by OnFilterRecord event, but it will operate rather slow).
If you do not need searching and your data will be rather large you should use Memo fields.

Varchar fields works faster than memo fields and usually achieve better compression rate.
Char fields provides better performance than Varchar fields, but requires more space, especially when there are many empty or short values. Varchar fields requires from 6 to 30 bytes per value for storing necessary link information and headers. Null values takes only 6 bytes reserved for link in the record, not null values will take additionally 20-24 bytes for headers depending on compressed data size is smaller than page size (24 bytes) or not (20 bytes plus size of empty space at last page).

© AidAim Software Accuracer: Client-server Database Single-file Database Delphi Database Embedded Databas