A common issue with Client-Server databases is the latency introduced for each query.
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 external tables, in BATCH mode.
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 Access /
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.
Featuring benchmark source code and nice performance charts.