Database creation

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;
  Table := TSynBigTableRecord.Create('FileName.ext','TableName');
  FieldText := Table.AddField('text',tftWinAnsi,[tfoIndex]);
  FieldInt := Table.AddField('Int',tftInt32,[tfoIndex,tfoUnique]);

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 disk space.
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 property.
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;

vari := Table.VariantVoid; vari.text := 'Some text'; := 12345; if Table.VariantAdd(vari)=0 then ShowMessage('Error adding record');
The above code will initialize the local vari instance with a custom variant type "knowing" the Table field layout, via the Table.VariantVoid property.
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.GetFieldValue(FieldText)='Some text');
vari := Table.VariantGet(aID);
assert(vari.Text='Some text');

Some dedicated methods are of course available to update or delete some records.

Search opportunities

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;
Count: integer;

assert(Table.Search(FieldText,'Some text',IDs,Count));

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 immediate.
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 unit.
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 laptop.
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 zip archive.
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. 

Feedback, full benchmak and comments are welcome on our forum.