Synopse Open Source

Software and hardware configuration

The following tables try to sum up all available possibilities, and give some benchmark (average objects/second for writing or reading).

In these tables:
- 'SQLite3 (file full/off/exc)' indicates use of the internal SQLite3 engine, with or without Synchronous := smOff and/or DB.LockingMode := lmExclusive;
- 'SQLite3 (mem)' stands for the internal SQLite3 engine running in memory;
- 'SQLite3 (ext ...)' is about access to a SQLite3 engine as external database , either as file or memory;
- 'TObjectList' indicates a TSQLRestServerStaticInMemory instance, either static (with no SQL support) or virtual (i.e. SQL featured via SQLite3 virtual table mechanism) which may persist the data on disk as JSON or compressed binary;
- 'NexusDB' is the free embedded edition, available from official site;
- 'Jet' stands for a MSAccess database engine, accessed via OleDB.
- 'Oracle' shows the results of our direct OCI access layer (SynDBOracle.pas);
- 'Zeos *' indicates that the database was accessed directly via the ZDBC layer;
- 'FireDAC *' stands for FireDAC library;
- 'UniDAC *' stands for UniDAC library;
- 'BDE *' when using a BDE connection;
- 'ODBC *' for a direct access to ODBC.

This list of database providers is to be extended in the future. Any feedback is welcome!

Numbers are expressed in rows/second (or objects/second). This benchmark was compiled with Delphi XE4, since newer compilers tends to give better results, mainly thanks to function in-lining (which was not existing e.g. in Delphi 6-7).

Note that these tests are not about the relative speed of each database engine, but reflect the current status of the integration of several DB libraries within the mORMot database access.

Benchmark was run on a Core i7 notebook, running Windows 7, with a standard SSD, including anti-virus and background applications:
- Linked to a shared Oracle 11.2.0.1 database over 100 Mb Ethernet;
- MS SQL Express 2008 R2 running locally in 64 bit mode;
- IBM DB2 Express-C edition 10.5 running locally in 64 bit mode;
- PostgreSQL 9.2.7 running locally in 64 bit mode;
- MySQL 5.6.16 running locally in 64 bit mode;
- Firebird embedded in revision 2.5.2;
- NexusDB 3.11 in Free Embedded Version.

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. When using high-head servers and storage, running on a tuned Linux configuration, you can expect even better numbers.

Tests were compiled with the Delphi XE4 32 bit mode target platform. Most of the tests do pass when compiled as a 64 bit executable, with the exception of some providers (like Jet), not available on this platform. Speed results are almost the same, only slightly slower; so we won't show them here.

You can compile the "15 - External DB performance" supplied sample code, and run the very same benchmark on your own configuration.
Feedback is welcome!

From our tests, the UniDAC version we were using had huge stability issues when used with DB2: the tests did not pass, and the DB2 server just hang processing the queries, whereas there was no problem with other libraries. It may have been fixed since, but you won't find any "UniDAC DB2" results in the benchmark below in the meanwhile.

Insertion speed

Here we insert 5,000 rows of data, with diverse scenarios:
- 'Direct' stands for a individual Client.Add() insertion;
- 'Batch' mode will be described 28;
- 'Trans' indicates that all insertion is nested within a transaction - which makes a great difference, e.g. with a SQlite3 database.

	Direct	Batch	Trans	Batch Trans
SQLite3 (file full)	462	356	95377	130086
SQLite3 (file off)	844	821	100389	136675
SQLite3 (file off exc)	28847	35316	102599	144258
SQLite3 (mem)	89456	120513	104249	146933
TObjectList (static)	314465	543892	326370	542652
TObjectList (virtual)	325393	545672	298846	545018
SQLite3 (ext full)	424	11297	102049	164636
SQLite3 (ext off)	830	21406	109706	189250
SQLite3 (ext off exc)	41589	180759	108481	192071
SQLite3 (ext mem)	101440	234576	113530	190142
ODBC SQLite3	492	11746	35367	82425
ZEOS SQlite3	494	11851	56206	85705
FireDAC SQlite3	26369	50306	49755	155115
UniDAC SQlite3	477	8725	26552	38756
ODBC Firebird	1495	18056	13485	17731
ZEOS Firebird	9733	13429	26348	30616
FireDAC Firebird	24233	52021	24791	52111
UniDAC Firebird	5986	14809	6522	14948
Jet	4235	4424	4954	5094
NexusDB	5998	15494	7687	18619
Oracle	226	56112	1133	52367
ODBC Oracle	236	1664	1515	7709
FireDAC Oracle	118	48575	1519	12566
UniDAC Oracle	164	5701	1215	2884
BDE Oracle	489	927	839	1022
MSSQL local	5246	54360	12988	62453
ODBC MSSQL	4911	18652	11541	20976
FireDAC MSSQL	5016	7341	11686	51242
UniDAC MSSQL	4392	29768	8649	33464
ODBC DB2	4792	48387	14085	70104
FireDAC DB2	4452	48635	11014	52781
ZEOS PostgreSQL	4196	26663	9689	38735
ODBC PostgreSQL	4068	19515	5130	27843
FireDAC PostgreSQL	4181	37000	10111	36483
UniDAC PostgreSQL	2705	18563	4442	22317
ODBC MySQL	3160	38309	10856	47630
ZEOS MySQL	3426	34037	12217	40186
FireDAC MySQL	3078	43053	10955	45781
UniDAC MySQL	3119	27772	11246	33288

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 unit and FireDAC library , BATCH process benefits of the array binding feature a lot (known as Array DML in FireDAC/AnyDAC).

For most engines, our ORM kernel is able to generate the appropriate SQL statement for speeding up bulk insertion. For instance:
- SQlite3, MySQL, PostgreSQL, MSSQL 2008, DB2, MySQL or NexusDB handle INSERT statements with multiple INSERT INTO .. VALUES (..),(..),(..)..;
- Oracle handles INSERT INTO .. INTO .. SELECT 1 FROM DUAL (weird syntax, isn't it?);
- Firebird implements EXECUTE BLOCK.

As a result, some engines show a nice speed boost when BatchAdd() is used. Even SQLite3 is faster when used as external engine, in respect to direct execution! This feature is at ORM/SQL level, so it benefits to any external database library. Of course, if a given library has a better implementation pattern (e.g. our direct Oracle or FireDAC with native array binding), it is used instead.

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)	27284	558721	550842
SQLite3 (file off)	26896	549450	526149
SQLite3 (file off exc)	128077	557537	535905
SQLite3 (mem)	127106	557537	563316
TObjectList (static)	300012	912408	913742
TObjectList (virtual)	303287	402706	866551
SQLite3 (ext full)	135380	267436	553158
SQLite3 (ext off)	133696	262977	543065
SQLite3 (ext off exc)	134698	264186	558596
SQLite3 (ext mem)	137487	259713	557475
ODBC SQLite3	19461	136600	201280
ZEOS SQlite3	33541	200835	306955
FireDAC SQlite3	7683	83532	112470
UniDAC SQlite3	2522	74030	96420
ODBC Firebird	3446	69607	97585
ZEOS Firebird	20296	91974	107229
FireDAC Firebird	2376	46276	56269
UniDAC Firebird	2189	66886	88102
Jet	2640	166112	258277
NexusDB	1413	120845	208246
Oracle	1558	120977	159861
ODBC Oracle	1620	43441	45764
FireDAC Oracle	1231	42149	54795
UniDAC Oracle	688	27083	30093
BDE Oracle	860	3870	4036
MSSQL local	10135	210837	437905
ODBC MSSQL	12458	147544	256502
FireDAC MSSQL	3776	72123	94091
UniDAC MSSQL	2505	93231	135932
ODBC DB2	7649	84880	124486
FireDAC DB2	3155	71456	88264
ZEOS PostgreSQL	8833	158760	223583
ODBC PostgreSQL	10361	85680	120913
FireDAC PostgreSQL	2261	58252	79002
UniDAC PostgreSQL	864	86900	122856
ODBC MySQL	10143	65538	82447
ZEOS MySQL	2052	171803	245772
FireDAC MySQL	3636	75081	105028
UniDAC MySQL	4798	99940	146968

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.

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 1,00,000 read requests per second, whatever database is used.

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, MS SQL, PostgreSQL, MySQL or IBM DB2. 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).

First of all, SQLite3 is still to be considered, even for a production server. Thanks to mORMot's architecture and design, this "embedded" database 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. Here, "embedded" is not restricted to "mobile", but sounds like a self-contained, zero-configuration proven engine.

Most recognized closed source databases are available:
- Direct access to Oracle gives impressive results in BATCH mode (aka array binding). It may be an obligation if your end-customer stores already its data in such a server, for instance, and want to leverage the licensing cost of its own IT solution. Oracle Express edition is free, but somewhat heavy and limited in terms of data/hardware size (see its licensing terms);
- MS SQL Server, directly accessed via OleDB (or ODBC) gives pretty good timing. A MS SQL Server 2008 R2 Express instance is pretty well integrated with the Windows environment, for a very affordable price (i.e. for free) - the LocalDB (MSI installer) edition is enough to start with, but also with data/hardware size limitation, just like Oracle Express;
- IBM DB2 is another good candidate, and the Express-C ("C" standing for Community) offers a no-charge opportunity to run an industry standard engine, with no restriction on the data size, and somewhat high hardware limitations (16 GB of RAM and 2 CPU cores for the latest 10.5 release) or enterprise-level features;
- NexusDB may be considered, if you have existing Delphi code and data - but it is less known and recognized as the its commercial competitors.

Open Source databases are worth considering, especially in conjunction with an Open Source framework like mORMot:
- MySQL is the well-known engine used by a lot of web sites, mainly with LAMP (Linux MySQL Apache PHP) configurations. Windows is not the best platform to run it, but it could be a fairly good candidate, especially in its MariaDB fork, which sounds more attractive those days than the official main version, owned by Oracle;
- PostgreSQL is an Enterprise class database, with amazing features among its Open Source alternatives, and really competes with commercial solutions. Even under Windows, we think it is easy to install and administrate, and uses less resource than the other commercial engines.
- Firebird gave pretty consistent timing, when accessed via Zeos/ZDBC. We show here the embedded version, but the server edition is worth considering, even if it has a less

To access those databases, OleDB, ODBC or ZDBC providers may also be used, with direct access. 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.

Therefore, the typical use may be the following (int. meaning "internal", ext. for "external", mem for "in-memory"):

Database	Use case
int. SQLite3 file	Created by default. General safe data handling, with amazing speed in "off exc" mode
int. SQLite3 mem	Created with :memory: file name. Fast data handling with no persistence (e.g. for testing or temporary storage)
TObjectList static	Created with StaticDataCreate. Best possible performance for small amount of data, without ACID nor SQL
TObjectList virtual	Created with VirtualTableRegister. Best possible performance for SQL over small amount of data (or even unlimited amount under Win64), if ACID is not required nor complex SQL
ext. SQLite3 file	Created with VirtualTableExternalRegister External back-end, e.g. for disk spanning
ext. SQLite3 mem	Created with VirtualTableExternalRegister Fast external back-end (e.g. for testing)
ext. Oracle / MS SQL / DB2 / PostgreSQL / MySQL / Firebird	Created with VirtualTableExternalRegister Fast, secure and industry standard back-ends; data can be shared outside mORMot
ext. NexusDB	Created with VirtualTableExternalRegister The free embedded version let the whole engine be included within your executable, and use any existing code, but SQlite3 sounds like a better option
ext. Jet	Created with VirtualTableExternalRegister Could be used as a data exchange format (e.g. with Office applications)
ext. Zeos	Created with VirtualTableExternalRegister Allow access to several external engines, with direct Zeos/ZDBC access which will by-pass the DB.pas unit and its TDataSet bottleneck - and we will also prefer an active Open Source project!
ext. FireDAC/UniDAC	Created with VirtualTableExternalRegister Allow access to several external engines, including the DB.pas unit and its TDataSet bottleneck

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.

Feedback is welcome in our forum, as usual.