Synopse Big Table 1.12a
Synopse Big Table is an open source Delphi unit for very fast data storage and access, using key/values pairs, or records organized with fields.
With this 1.12a version, the unit has evolved into a true field-oriented
database, with two new classes:
- TSynBigTableRecord to store an unlimited number of records with fields;
- TSynBigTableMetaData to store any data (pictures, HTML, text) associated with metadata fields.
Both classes handle variable-length storage of integers, floats, currency,
text (Unicode or not) with a field name. On-the-fly field adding, integrated
indexing and search capabilities.
Data access can be either fast direct access, or via late-binding (i.e. use Record.Field in your Delphi code).
Classic Key/Value storage is always possible via TSynBigTable or TSynBigTableString, but is now faster and safer. A few issues were corrected.
In order to understand how the two new classes work, we will create a new database with some fields:
var Table: TSynBigTableRecord; FieldText, FieldInt: TSynTableFieldProperties; begin Table := TSynBigTableRecord.Create('FileName.ext','TableName'); FieldText := Table.AddField('text',tftWinAnsi,[tfoIndex]); FieldInt := Table.AddField('Int',tftInt32,[tfoIndex,tfoUnique]); Table.AddFieldUpdate;
The database will be stored in the FileName.ext file,
and will have internally TableName as table name (this table name
will be used later, inside our main framework interface those classes via SQL -
you won't have to care about this by now, anyway).
Its first field will be named TEXT, will contain some ansi text (we don't need true Unicode here, and WinAnsi will save some disk space). It will use an index.
The second field is named INT, and will contain some integer 32 bit value. It will have also an index, and during record creation, it will be checked that every value is unique.
Of course, if the file already exists, the AddField calls won't do
anything: the field layout is stored in the file, so the fields won't be
created each time - only if needed.
The AddFieldUpdate method must be called at last, because if some fields were just added from a file already containing some data, this method will process the rows in order to prepare the storage of any new field.
It's worth noting that the storage layout on disk will follow a
"performance" order: fixed size fields will be placed first in every record,
and indexed fields will also be first. Layout on disk won't follow the order in
which the AddField method has been called. You can even store integer
values in variable-length. It will be a bit slower, but it could save a lot of
In all cases, just know that it was designed to be fast, and use as less disk space as possible.
Fields and records handling
OK. We have a database with fields.
But how do we handle the data?
There are several ways of handling fields: via direct access or via late-binding.
Direct access will use a TSynTableData record type to store the data of a table row:
var rec: TSynTableData;
rec.Init(Table.Table); rec.Field['TEXT'] := 'Some text'; rec.SetFieldValue(FieldInt,12345); aID := Table.RecordAdd(rec); if aID=0 then ShowMessage('Error adding record');
The above code will initialize the local rec instance to work
with the Table field layout, via the Table.Table
Note that the rec instance is an object allowed on stack: you don't have to call any rec.Free or add any try..finally block.
Then a value is set to the TEXT field. The rec.Field[fieldname] can be read or set with any variant value.
The INT field is accessed direcly, via the SetFieldValue method (which is faster than the Field method, because it's not necessary to search for the field name).
Then the record content is added to the database (RecordAdd returns the ID of the added row).
Late-binding makes use of a custom variant type:
var vari: Variant;The above code will initialize the local vari instance with a custom variant type "knowing" the Table field layout, via the Table.VariantVoid property.
vari := Table.VariantVoid; vari.text := 'Some text'; vari.int := 12345; if Table.VariantAdd(vari)=0 then ShowMessage('Error adding record');
Then, you can access to the record properties, just by using their name. The custom variant type will retrieve the TSynTableFieldProperties field by late-binding (i.e. during the execution). An exception will be raised in case of wrong field name.
Of course, this has a cost: using this variant type will be slower than directTSynTableData record access (and the faster will be TSynTableData.SetFieldSBFValue method, because it won't use any variant). But for common use, using a variant could make your code cleaner.
You can retrieve a record field content by using one of the two types:
rec := Table.RecordGet(aID); assert(rec.ID=aID); assert(rec.GetFieldValue(FieldText)='Some text'); vari := Table.VariantGet(aID); assert(vari.ID=aID); assert(vari.Text='Some text');
Some dedicated methods are of course available to update or delete some records.
Both classes offer advanced search features.
They allow to fast iterate through all records for a value, or can use an internal index, for immediate retrieval:
var IDs: TIntegerDynArray;
As shown in the above code, you can search for records matching a specified
field value. If an index was created with the field (but you can also create
later an index to any existing field), search will use this one, and will be
The Search method returns its results in a array of integer, containing all matching IDs.
Speed is, with the moderate disk space usage, one major goal of this
Thanks to its unique design, I think you have at hand the fastest database engine for Delphi. Much faster than any SQL engine around, in all cases.
Creating 1,000,000 records with some text and an integer value, both fields
using an index, and the integer field set as unique is less than 880 ms on my
Reading all 1,000,000 records, and checking both field values take 220 ms in direct, 360 ms using TSynTableData, and 1560 ms using the late-binding (i.e. using a variant type - which is, as expected, the slower but cleaner method).
Writing the content to file is about 70 ms. Opening a file 30 ms. Adding a field then recreating the file layout 470 ms.
Searching 50 text values iterating takes 1970 ms; 200 text values using an index only 0.3 ms.
Searching 50 integer values iterating takes 1660 ms; 200 integer values using an index only 0.1 ms.
File size is only 19 MB big, including all data, indexes, and field layout.
We provide a sample executable with the source code, so that you could test it on your own PC.
Get the source and make yourself your idea
Available from our Source Code
repository and from a
Compiles with Delphi 6 up to Delphi XE (fully Unicode-compatible, even before Delphi 2009).
Licensed under a MPL/GPL/LGPL tri-license, ready to be embedded in any application.