Synopse

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 so slow (less than 10 objects per second) 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 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 up to 400 objects per second.

Therefore, the typical use may be the following:

In the above list, the MS SQL Server is not integrated, but may be used instead of Oracle (minus the fact that BULK insert is not implemented yet for it, whereas array binding boosts Oracle writing BATCH process performance by 100 times).

Benchmark was run on a good old Core 2 Duo workstation (no SSD), with anti-virus and background applications, over a 100 Mb corporate network, linked to a shared Oracle 11g database.
So it was a development environment, very similar to production site, not dedicated to give best performance.
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.

Results were created with the same software sample as used for Oracle Array Binding benchmarking.
So you can compile the provided 15 - External DB performance sample code, and check by your own the framework speed.

Insertion speed

Read speed

For both writing and reading, TObjectList / TSQLRestServerStaticInMemory engine gives impressive results, but have the weakness of being in-memory: you can write its content on disk either as JSON or as compressed binary, but every transaction is not ACID in case of power loss. With the latest version of the framework, TSQLRestServerStaticInMemory does also feature very efficient hashed indexes for very fast lookup of values. 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!

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

Since all the results above are on an average desktop computer, you may find out incredible speed achieved with a Server hardware, e.g. using SSD or a NAS storage. Just imagine how fast your server side may be, when using mORMot and Delphi instead of other technologies! And more clients on the same hardware often allow increasing ROI.

Feedback is welcome on our forum, as usual.