Insertion speed
Here we test insertion of some records, for most of our supplied
engines.
We did the test with UNIK conditional undefined, i.e. with no
index of the Name field.
A Core i7 notebook has been used, as hardware platform.
Oracle 11g database is remotely accessed over a corporate network, so latency
and bandwidth is not optimal.
The hardrive is a SSD this time - so we will see how it affects the
results.
| SQLite3 (file full) |
SQLite3 (file off) |
SQLite3 (mem) |
TObjectList (static) |
TObjectList (virtual) |
SQLite3 (ext file full) |
SQLite3 (ext file off) |
SQLite3 (ext mem) |
Oracle | ODBC Oracle | Jet | |
| Direct | 501 | 911 | 81870 | 281848 | 288234 | 548 | 952 | 72697 | 518 | 512 | 4159 |
| Batch | 523 | 891 | 102614 | 409836 | 417257 | 557 | 868 | 91617 | 77155 | 509 | 4441 |
| Trans | 90388 | 95884 | 96612 | 279579 | 286188 | 99681 | 70950 | 105674 | 1024 | 1432 | 4920 |
| Batch Trans | 110869 | 117376 | 125190 | 412813 | 398851 | 127424 | 126627 | 121368 | 62601 | 1019 | 4926 |
Performance gain is impressive, especially for "ODBC Oracle" and also "OleDB
Jet".
Since Jet/MSAccess is a local engine, it is faster than Oracle for one record
retrieval - it does not suffer from the network latency. But it is faster than
SQlite3 at insertion, due to a multi-thread design - which is perhaps
less ACID nor proven.
Note that this hardware configuration run on a SSD, so even "SQLite3 (file
full)" configuration is very much boosted - about 3 times faster.
Our direct Oracle access classes achieve more than 77,000 inserts per second in
BATCH mode (using the Array Binding feature).
Direct TObjectList in-memory engine reaches amazing speed, when
used in BATCH mode - more than 400,000 inserts per second!
Read speed
| SQLite3 (file full) |
SQLite3 (file off) |
SQLite3 (mem) |
TObjectList (static) |
TObjectList (virtual) |
SQLite3 (ext file full) |
SQLite3 (ext file off) |
SQLite3 (ext mem) |
Oracle | ODBC Oracle | Jet | |
| By one | 26777 | 26933 | 122016 | 298400 | 301041 | 135413 | 133571 | 131877 | 1289 | 1156 | 2413 |
| All Virtual | 429331 | 427423 | 447227 | 715717 | 241289 | 232385 | 167420 | 202839 | 63473 | 35029 | 127772 |
| All Direct | 443773 | 433463 | 427094 | 711035 | 700574 | 432189 | 334179 | 340136 | 90184 | 39485 | 186164 |
Reading speed was also increased. ODBC results have the biggest
improvement.
Server-side statement cache for Oracle makes individual reading of records 2
times faster. Wow.
The SQLite3 engine is still the more reactive SQL database here, when
it comes to reading.
Of course, direct TObjectList engine is pretty fast - more than
700,000 records per second.
Feedback is welcome on our forum, as usual.
