For real applications, retrieving objects per ID is not enough.
Your project may have the need to retrieve objects by a textual field, e.g. a
name or identifier.
In this case, you can specify a published property of the
TSQLRecord
as stored false
, and it will be
defined as an unique column in
the underlying database.
For instance, in the
latest version of our performance benchmark sample code, you can define the
UNIK
conditional to define both LastName
and
FirstName
properties as unique:
type TSQLRecordSample = class(TSQLRecord) private fFirstName: RawUTF8; fLastName: RawUTF8; fAmount: currency; fBirthDate: TDateTime; fLastChange: TModTime; fCreatedAt: TCreateTime; published property FirstName: RawUTF8 index 40 read fFirstName write fFirstName {$ifdef UNIK}stored false{$endif}; property LastName: RawUTF8 index 40 read fLastName write fLastName {$ifdef UNIK}stored false{$endif}; property Amount: currency read fAmount write fAmount; property BirthDate: TDateTime read fBirthDate write fBirthDate; property LastChange: TModTime read fLastChange; property CreatedAt: TCreateTime read fCreatedAt write fCreatedAt; end;
During insertion or update of records, the database will have to check for
uniqueness of those column values. It will have an additional performance cost,
since a search of the new value is to be performed among existing values.
In order to speed-up the process, a so-called index is created at the
database level.
As a consequence, further lookup using this property will benefit for this
index, and will be much faster than a classic loop throughout all
data.
In the mORMot core, we just made some modifications related to this feature:
- External tables are now able to create properly UNIQUE fields at database level, as expected;
- A hashed-based
index feature has been added to the
fast
TSQLRestServerStaticInMemory
internal engine, and insertion will have no speed penalty any more.