Insertion speed

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

  • 'Direct' stands for a individual Client.Add() insertion;
  • 'Batch' mode, has already been described in this blog;
  • '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, including anti-virus and background applications, over a 100 Mb corporate network, linked to a shared Oracle 11g database. A local instance of MSSQLExpress 2008 R2 was running locally. 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) 483 473 78206 121592
SQLite3 (file off) 879 843 100851 111167
SQLite3 (file off exc) 31703 35022 100276 133109
SQLite3 (mem) 86961 114678 104177 138354
TObjectList (static) 336700 562239 330425 547405
TObjectList (virtual) 324296 565995 319795 554754
SQLite3 (ext full) 503 467 104574 136892
SQLite3 (ext off) 890 335 96420 135744
SQLite3 (ext off exc) 44634 50908 114064 154221
SQLite3 (ext mem) 101578 137540 116563 155322
FireDAC SQlite3 26660 53033 53320 151754
UniDAC SQlite3 345 365 27390 37913
ZEOS SQlite3 499 493 56790 66956
ZEOS Firebird 6535 13545 26531 30136
FireDAC Firebird 25177 51917 24522 51880
UniDAC Firebird 6081 7061 6338 7096
Jet 4224 4390 4844 4984
NexusDB 5998 6549 7668 8491
Oracle 566 65183 1223 64413
ODBC Oracle 589 482 1085 1328
BDE Oracle 489 511 1024 1003
ZEOS Oracle 599 657 1330 1464
FireDAC Oracle 570 45405 1287 48686
UniDAC Oracle 391 533 1135 1159
MSSQL local 4619 5472 13373 14364
ODBC MSSQL 5067 5200 11752 12258
FireDAC MSSQL 4979 7521 11925 51100
UniDAC MSSQL 4407 4467 8867 9936
ZEOS PostgreSQL 2982 2994 6223 6028
FireDAC PostgreSQL 2796 27638 1466 27171

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. Note that by default, the FireDAC library set both options, so results above are to be compared with "SQLite3 off exc" rows.

For both our direct Oracle access SynDBOracle.pas library and FireDAC, Batch process benefit of the array binding feature a lot (known as Array DML in FireDAC/AnyDAC).

Reading speed

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) 27403 551571 559346
SQLite3 (file off) 26840 555062 557537
SQLite3 (file off exc) 122537 555185 541418
SQLite3 (mem) 124750 551328 564206
TObjectList (static) 299868 945537 935803
TObjectList (virtual) 302114 433463 891583
SQLite3 (ext full) 131960 262660 541887
SQLite3 (ext off) 132268 264200 548666
SQLite3 (ext off exc) 132886 265561 504693
SQLite3 (ext mem) 133911 255036 534302
FireDAC SQlite3 7749 79058 110338
UniDAC SQlite3 2581 74839 98460
ZEOS SQlite3 42336 208489 312617
ZEOS Firebird 20626 95496 129954
FireDAC Firebird 2370 48066 57449
UniDAC Firebird 2229 69845 90064
Jet 2643 159576 258678
NexusDB 1413 120845 208246
Oracle 1464 123031 159459
ODBC Oracle 1269 42528 49029
BDE Oracle 860 3870 4036
ZEOS Oracle 913 33167 34806
FireDAC Oracle 1195 44039 53335
UniDAC Oracle 657 26212 25770
MSSQL local 10398 218818 425785
ODBC MSSQL 13062 137241 256581
FireDAC MSSQL 4119 76747 102984
UniDAC MSSQL 2770 91431 135394
ZEOS PostgreSQL 8005 6896 159933
FireDAC PostgreSQL 2222 61156 81284

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. External database access is only required when data is expected to be shared with other processes.

In the above table, it appears that all libraries based on DB.pas are slower than the others for reading speed. In fact, TDataSet sounds to be a real bottleneck, due to its internal data marshalling. Even FireDAC, which is known to be very optimized for speed, is limited by the TDataSet structure. Our direct classes, or even ZEOS/ZDBC performs better, since they are able to output JSON content with no additional marshalling.

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.

As a consequence, search of non-unique values may be slow: the engine has to loop through all rows of data. But for unique values (defined as stored AS_UNIQUE), both insertion and search speed is awesome, due to its optimized O(1) hash algorithm - see the following benchmark, especially the "By name" row for "TObjectList" columns, which correspond to a search of an unique RawUTF8 property value via this hashing method.

SQLite3 (file full) SQLite3 (file off) SQLite3 (mem) TObjectList (static) TObjectList (virt.) SQLite3 (ext file full) SQLite3 (ext file off) SQLite3 (ext mem) Oracle Jet
By one 10461 10549 44737 103577 103553 43367 44099 45220 901 1074
By name 9694 9651 32350 70534 60153 22785 22240 23055 889 1071
All Virt. 167095 162956 168651 253292 118203 97083 90592 94688 56639 52764
All Direct 167123 144250 168577 254284 256383 170794 165601 168856 88342 75999

Above table results were run on a Core 2 duo laptop, so numbers are lower than with the previous tables.

Analysis and use case proposal

When declared as virtual table (via a VirtualTableRegister call), you have the full power of SQL (including JOINs) at hand, with incredibly fast CRUD operations: 100,000 requests per second for objects read and write, including serialization and Client-Server communication!

Some providers are first-class citizens to mORMot, like SQLite3, Oracle, or MS SQL. You can connect to them without the bottleneck of the DB.pas unit, nor any restriction of your Delphi license (a Starter edition is enough). For instance, SQLite3 could be used as main database engine for a client-server application with heavy concurrent access - if you have doubts about its scaling abilities, see this blog article. Direct access to Oracle is also available, with impressive results in BATCH mode (aka array binding). MS SQL Server, directly accessed via OleDB (or ODBC) gives pretty good timing, and a MS SQL Server 2008 R2 Express instance is a convincing option, for a very offerdable price (i.e. for free) - the LocalDB (MSI installer) edition is enough to start with. Any other OleDB, ODBC or ZDBC providers may also be used, with direct access. For instance, Firebird embedded gives pretty consistent timing, when accessed via Zeos/ZDBC.

But mORMot is very open-minded: you can use any DB.pas provider, e.g. FireDAC, UniDAC, DBExpress, NexusDB or even the BDE, but with the additional layer introduced by using a TDataSet instance, at reading.

Note that all those tests were performed locally and in-process, via a TSQLRestClientDB instance. For both insertion and reading, a Client-Server architecture (e.g. using HTTP/1.1 for mORMot clients) will give even better results for BATCH and retrieve all modes.

During the tests, internal caching was disabled, so you may expect speed enhancements for real applications, when data is more read than written: for instance, when an object is retrieved from the cache, you achieve more than 700,000 read requests per second, whatever database is used.

Therefore, the typical use may be the following:

Database Created by Use
int. SQLite3 file default General safe data handling, with amazing speed in "off exc" mode
int. SQLite3 mem :memory: Fast data handling with no persistence (e.g. for testing or temporary storage)
TObjectList static StaticDataCreate Best possible performance for small amount of data, without ACID nor SQL
TObjectList virtual VirtualTableRegister Best possible performance for small amount of data, if ACID is not required nor complex SQL
ext. SQLite3 file VirtualTableExternalRegister External back-end, e.g. for disk spanning
ext. SQLite3 mem VirtualTableExternalRegister Fast external back-end (e.g. for testing)
ext. Oracle / MS SQL / Firebird VirtualTableExternalRegister Fast, secure and industry standard; data can be shared outside mORMot
ext. NexusDB VirtualTableExternalRegister The free embedded version let the whole engine be included within your executable, and insertion speed is higher than SQLite3, so it may be a good alternative if your project mostly insert individual objects - using a batch within a transaction let SQlite3 be the faster engine
ext. Jet VirtualTableExternalRegister Could be used as a data exchange format (e.g. with Office applications)
ext. Zeos/FireDAC/UniDAC VirtualTableExternalRegister Allow access to several external engines, with some advantages for Zeos, since direct ZDBC access will by-pass the DB.pas unit and its TDataSet bottleneck - and we will also prefer an active Open Source project!

Whatever database back-end is used, don't forget that mORMot design will allow you to switch from one library to another, just by changing a TSQLDBConnectionProperties class type. And note that you can mix external engines, on purpose: you are not tied to one single engine, but the database access can be tuned for each ORM table, according to your project needs.