To be more specific:
SQlite3, MySQL, PostgreSQL, MSSQL 2008 or NexusDB handle
INSERT
statements with multiple VALUES
, in the
following SQL-92 standard
syntax, using parameters:
INSERT INTO TABLE (column-a, [column-b, ...]) VALUES ('value-1a', ['value-1b', ...]), ('value-2a', ['value-2b', ...]), ...
Oracle implements the weird but similar syntax
(note the mandatory SELECT
at the end):
INSERT ALL INTO phone_book VALUES ('John Doe', '555-1212') INTO phone_book VALUES ('Peter Doe', '555-2323') SELECT * FROM DUAL;
Firebird implements its own syntax:
execute block as begin INSERT INTO phone_book VALUES ('John Doe', '555-1212'); INSERT INTO phone_book VALUES ('Peter Doe', '555-2323'); end
As a result, some engines show a nice speed boost when used in BATCH.
Even SQLite3 is faster when used as external engine, in respect to
direct execution, in respect to direct execution of individual prepared
statements in loop!
Here are some insertion results, to compare with the previous benchmark, which did not include these enhancements:
Direct | Batch | Trans | Batch Trans | |
SQLite3 (file full) | 488 | 463 | 97498 | 126256 |
SQLite3 (file off) | 789 | 815 | 101010 | 130561 |
SQLite3 (file off exc) | 31376 | 35785 | 104410 | 136328 |
SQLite3 (mem) | 88070 | 106981 | 106215 | 144270 |
TObjectList (static) | 308584 | 545732 | 311837 | 535733 |
TObjectList (virtual) | 308413 | 539548 | 316997 | 527537 |
SQLite3 (ext full) | 308 | 12151 | 107469 | 170636 |
SQLite3 (ext off) | 776 | 22404 | 111819 | 188316 |
SQLite3 (ext off exc) | 42213 | 182561 | 111642 | 197464 |
SQLite3 (ext mem) | 98531 | 228634 | 112004 | 227489 |
ZEOS SQlite3 | 497 | 12071 | 56489 | 72720 |
ODBC SQlite3 | 509 | 12480 | 38996 | 82581 |
FireDAC SQlite3 | 24992 | 50065 | 21985 | 156887 |
UniDAC SQlite3 | 469 | 8981 | 27667 | 39239 |
NexusDB | 5996 | 15494 | 7687 | 18619 |
ZEOS Firebird | 12732 | 13848 | 27456 | 30724 |
ODBC Firebird | 1745 | 18366 | 14419 | 18993 |
FireDAC Firebird | 24000 | 50329 | 24050 | 51423 |
UniDAC Firebird | 6373 | 14801 | 6474 | 14675 |
Jet | 4252 | 4561 | 5016 | 5208 |
Oracle | 310 | 42327 | 1046 | 61661 |
ODBC Oracle | 337 | 3962 | 1356 | 5197 |
FireDAC Oracle | 458 | 35160 | 1451 | 37204 |
UniDAC Oracle | 289 | 3065 | 1140 | 5747 |
BDE Oracle | 489 | 927 | 839 | 1022 |
MSSQL local | 5266 | 54417 | 13659 | 62706 |
ODBC MSSQL | 5050 | 18739 | 11804 | 20796 |
FireDAC MSSQL | 4989 | 7315 | 11267 | 50520 |
UniDAC MSSQL | 4404 | 30845 | 8879 | 34933 |
This feature is at ORM level, so it benefits to any external database
library.
Of course, if a given library has a better option (e.g. our direct
Oracle or FireDAC array binding), it is used instead.
You can note that we included access to Firebird embedded via
ODBC, using the
official driver.
And also SQLite3 access via ODBC, using this nice full-featured BSD licensed
driver.
Sounds like a not so optimized solution, e.g. in respect to ZDBC/ZEOS
direct connection.
But nice show case of ODBC connection with mORMot.
Reading speed is not affected by this modification, so we won't publish new
data here.
Note that now our native access to external databases outperforms any
third-party drivers, with the only exception of Firebird, which is
still most efficiently accessed via FireDAC.
The SAD
1.18 pdf includes the latest benchmark.
If you want to use a map/reduce algorithm in your application, or the DDD's Unit Of Work pattern, in addition to ORM data access, all those enhancements may speed up a lot your process. Reading and writing huge amount of data has never been so fast and easy: you may even be tempted to replace stored-procedure process by high-level code implemented in your Domain service. N-tier separation would benefit from it.
Feedback is welcome on our forum, as usual.