SQLite3 performance benchmark

Here we insert 5,000 rows of data, with diverse scenarios:

  • 'Direct' stands for a individual Client.Add() insertion;
  • 'Batch' mode regrouping data rows;
  • 'Trans' indicates that all insertion is nested within a transaction - which makes a great difference, e.g. with a SQlite3 database.

Benchmark was run on a Core i7 notebook, with standard SSD.
So it was a development environment, very similar to low-cost production site, not dedicated to give best performance.
During the process, CPU was noticeable used only for SQLite3 in-memory and TObjectList - most of the time, the bottleneck is not the CPU, but the storage or network.
As a result, rates and timing may vary depending on network and server load, but you get results similar to what could be expected on customer side, with an average hardware configuration.

  Direct Batch Trans Batch Trans
SQLite3 (file full) 503 399 96391 123064
SQLite3 (file off) 853 930 99534 130907
SQLite3 (file off exc) 31829 35798 101874 132752
SQLite3 (mem) 85803 109641 103976 135332
TObjectList (static) 321089 548365 312031 547105
TObjectList (virtual) 314366 513136 316676 571232
SQLite3 (ext full) 451 511 12092 137249
SQLite3 (ext off) 971 909 108133 144475
SQLite3 (ext off exc) 42805 51256 113155 150829
SQLite3 (ext mem) 97344 121400 113229 153256

Due to its ACID implementation, SQLite3 process on file waits for the hard-disk to have finished flushing its data, therefore it is the reason why it is slower than other engines at individual row insertion (less than 10 objects per second with a mechanical hardrive instead of a SDD) outside the scope of a transaction.

So if you want to reach the best writing performance in your application with the default engine, you should better use transactions and regroup all writing into services or a BATCH process.
Another possibility could be to execute DB.Synchronous := smOff and/or DB.LockingMode := lmExclusive at SQLite3 engine level before process: in case of power loss at wrong time it may corrupt the database file, but it will increase the rate by a factor of 50 (with hard drive), as stated by the "off" and off exc" rows of the table - see below.

Now the same data is retrieved via the ORM layer:

  • 'By one' states that one object is read per call (ORM generates a SELECT * FROM table WHERE ID=? for Client.Retrieve() method);
  • 'All *' is when all 5000 objects are read in a single call (i.e. running SELECT * FROM table from a FillPrepare() method call), either forced to use the virtual table layer, or with direct static call.

Here are some reading speed values, in objects/second:

  By one All Virtual All Direct
SQLite3 (file full) 26936 514456 531858
SQLite3 (file off) 27116 538735 428302
SQLite3 (file off exc) 122417 541125 541653
SQLite3 (mem) 119314 539781 545494
TObjectList (static) 303398 529661 799232
TObjectList (virtual) 308109 403323 871080
SQLite3 (ext full) 137525 264690 546806
SQLite3 (ext off) 134807 262123 531011
SQLite3 (ext off exc) 133936 261574 536941
SQLite3 (ext mem) 136915 258732 544069

The SQLite3 layer gives amazing reading results, which makes it a perfect fit for most typical ORM use. When running with DB.LockingMode := lmExclusive defined (i.e. "off exc" rows), reading speed is very high, and benefits from exclusive access to the database file - see below.
External database access is only required when data is expected to be shared with other processes.

For both writing and reading, TObjectList / TSQLRestServerStaticInMemory engine gives impressive results, but has the weakness of being in-memory, so it is not ACID by design, and the data has to fit in memory. Note that indexes are available for IDs and stored AS_UNIQUE properties.

Synchronous writing

You can overwrite the first default ACID behavior by setting the TSQLDataBase.Synchronous property to smOff instead of the default smFull setting.
When Synchronous is set to smOff, SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, some operations are as much as 50 or more times faster with this setting.

When the tests performed during benchmarking use Synchronous := smOff, "Write one" speed is enhanced from 8-9 rows per second into about 400 rows per second, on a physical hard drive (SSD or NAS drives may not suffer from this delay).

So depending on your application requirements, you may switch Synchronous setting to off.

To change the main SQLite3 engine synchronous parameter, you may code for instance:

Client := TSQLRestClientDB.Create(Model,nil,MainDBFileName,TSQLRestServerDB,false,'');
Client.Server.DB.Synchronous := smOff;

Note that this setting is common to a whole TSQLDatabase instance, so will affect all tables handled by the TSQLRestServerDB instance.

But if you defined some SQLite3 external tables, you can define the setting for a particular external connection, for instance:

Props := TSQLDBSQLite3ConnectionProperties.Create(DBFileName,'''','');
VirtualTableExternalRegister(Model,TSQLRecordSample,Props,'SampleRecord');
Client := TSQLRestClientDB.Create(Model,nil,MainDBFileName,TSQLRestServerDB,false,'');
TSQLDBSQLite3Connection(Props.MainConnection).Synchronous := smOff;

File locking

You can overwrite the second default ACID behavior by setting the TSQLDataBase.LockingMode property to LockingMode instead of the default lmNormal setting.
When LockingMode is set to lmExclusive, SQLite will lock the database file for exclusive use during the whole session. It will prevent other processes (e.g. database viewer tools) to access the file at the same time, but small write transactions will be much faster, by a factor usually greater than 40. Bigger transactions involving several hundredths/thousands of INSERT won't be accelerated - but individual insertions will have a major speed up.

To change the main SQLite3 engine locking mode parameter, you may code for instance:

Client := TSQLRestClientDB.Create(Model,nil,MainDBFileName,TSQLRestServerDB,false,'');
Client.Server.DB.LockingMode := lmExclusive;

Note that this setting is common to a whole TSQLDatabase instance, so will affect all tables handled by the TSQLRestServerDB instance.

But if you defined some SQLite3 external tables, you can define the setting for a particular external connection, for instance:

Props := TSQLDBSQLite3ConnectionProperties.Create(DBFileName,'''','');
VirtualTableExternalRegister(Model,TSQLRecordSample,Props,'SampleRecord');
Client := TSQLRestClientDB.Create(Model,nil,MainDBFileName,TSQLRestServerDB,false,'');
TSQLDBSQLite3Connection(Props.MainConnection).LockingMode := lmExclusive;

In fact, exclusive file locking improves the reading speed by a factor of 4 (in case of individual row retrieval).
As such, defining LockingMode := lmExclusive without Synchronous := smOff could be of great benefit for a server which purpose is mainly to serve ORM content to clients.

Performance tuning

By default, the slow but truly ACID setting will be used with mORMot, just as with SQlite3.
We do not change this policy (as FireDAC library does, for instance), since it will ensure best safety, in the expense of slow writing outside a transaction.

The best performance will achieved by combining the two previous options, as such:

Client := TSQLRestClientDB.Create(Model,nil,MainDBFileName,TSQLRestServerDB,false,'');
Client.Server.DB.LockingMode := lmExclusive;
Client.Server.DB.Synchronous := smOff;

Or, for external tables:

Props := TSQLDBSQLite3ConnectionProperties.Create(DBFileName,'''','');
VirtualTableExternalRegister(Model,TSQLRecordSample,Props,'SampleRecord');
Client := TSQLRestClientDB.Create(Model,nil,MainDBFileName,TSQLRestServerDB,false,'');
TSQLDBSQLite3Connection(Props.MainConnection).Synchronous := smOff;
TSQLDBSQLite3Connection(Props.MainConnection).LockingMode := lmExclusive;

If you can afford loosing some data in very rare border case, or if you are sure your hardware configuration is safe (e.g. if the server is connected to a power inverter and has RAID disks) and that you have backups at hand, setting Synchronous := smOff would help your application scale for writing. Setting LockingMode := lmExclusive will benefit of both writing and reading speed.
Consider using an external and dedicated database (like Oracle or MS SQL) if your security expectations are very high, and if the default safe but slow setting is not enough for you.

In all cases, do not forget to perform backups as often as possible (at least several times a day).
You may use TSQLRestServerDB.Backup or TSQLRestServerDB.BackupGZ methods for a fast backup of a running database. Adding a backup feature on the server side is as simple as running:

Client.Server.BackupGZ(MainDBFileName+'.gz');

Server will stop working during this phase, so a lower-level backup mechanism could be used instead, if you need 100% of service availability. Using an external database would perhaps keep you main mORMot database small in size, so that its backup time will remain unnoticeable on the client side.

Note that with the current implementation, low-level backup is not working as expected on the Win64 platform. The error seems to be at the SQlite3 64 bit library level, since it is not able to release all internal instance statements before backup. We were not able to fix this issue yet.

Feedback is welcome on our forum, as usual.