A common issue with Client-Server databases is the latency introduced for
For example, suppose you have a requirement to first collect some
information from your application’s users and then insert that information into
a table in Oracle Database.
The first obvious option is to insert these multiple rows into the table
through a loop in your program. This loop iterates over the data to be inserted
and does what is known as a single-row insert , because the application sends
one single row of data to the database at a time. Due to the network latency
(typically around 1 ms over a corporate network), it would achieve not more
than 500-600 requests per second to let the work done, since for each INSERT, a
so-called round-trip occurs: a message is sent to
Oracle, then a response is sent back to the client.
You have another option for inserting multiple rows of data into the table
that reduces the number of round-trips and improves application
performance, database performance, and network resource use. Rather than having
the application send a single row of data to the database at a time, it can use
array binding to send the data in batches of rows. Therefore, you
reduce a lot the number of round-trips to be processed, and enhance
performance by a factor of about 100.
SynDB unit has been enhanced to introduce new
TSQLDBStatement.BindArray() methods, introducing array binding for
faster database batch modifications (only implemented in
SynDBOracle by now - but MS SQL has a similar feature called OleDB
It is available from the ORM side or mORMot, when working with
tables, in BATCH
Thanks to this enhancement, inserting records within Oracle comes
from 400-500 rows per second to more than 50000 rows per second!
It was also a good opportunity to speed up the BATCH process globally, and to
benchmark our Oracle back-end against existing external databases,
i.e. SQLite3 (as file or in-memory), and Jet / MS
Note that this article scope is only about virtual tables linked to external
classes will access directly to the SQLite3 engine or in-memory
TList, so speed will be even higher than the below values.
benchmark source code and nice performance charts.