Oracle Array Binding and BATCH performance
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.
The ORM record used for this test is defined as such:
type TSQLRecordSample = class(TSQLRecord) private fFirstName: RawUTF8; fLastName: RawUTF8; fAmount: currency; fBirthDate: TDateTime; fLastChange: TModTime; fCreatedAt: TCreateTime; published property FirstName: RawUTF8 index 40 read fFirstName write fFirstName; property LastName: RawUTF8 index 40 read fLastName write fLastName; property Amount: currency read fAmount write fAmount; property BirthDate: TDateTime read fBirthDate write fBirthDate; property LastChange: TModTime read fLastChange; property CreatedAt: TCreateTime read fCreatedAt write fCreatedAt; end;
So this is something you may have to use in your application, not a blank
table with one row.
Note that we have specified
index 40 for
LastName properties, to ensure that it would be sized to 40
widechars (without this
index attribute, it would have created a
NCLOB, which is much slower than a NVARCHAR2).
The process consisted in inserting 5000 rows, with or without a BATCH mode, nested with or without a transaction.
The benchmark code is similar to this:
Props := PropsClass.Create(Server,aDatabaseName,aUserID,aPassWord); try VirtualTableExternalRegister(Model,TSQLRecordSample,Props,'SampleRecord'); Client := TSQLRestClientDB.Create(Model,nil,':memory:',TSQLRestServerDB,false,''); try Client.Server.CreateMissingTables; if aUseTransactions then Client.TransactionBegin(TSQLRecordSample); if not aUseBatch then SetLength(Res,Stat.fNumberOfElements); Value.BirthDate := EncodeDate(1900,1,1); for i := 1 to Stat.fNumberOfElements do begin Value.Amount := i*0.01; Value.LastName := Int32ToUtf8(i); Value.FirstName := 'Nam'#$c3#$a9' '+Value.LastName; // 'Namé' in UTF-8 if aUseBatch then begin if Client.BatchCount=0 then Client.BatchStart(TSQLRecordSample); Client.BatchAdd(Value,true); end else Res[i-1] := Client.Add(Value,true); Value.BirthDate := Value.BirthDate+1; end; if aUseBatch then Client.BatchSend(Res); if aUseTransactions then Client.Commit;
For SQlite3 without transactions, it inserted only 30 rows instead of 5000, since SQLite3 is by design very slow outside a transaction, due to its ACID behavior which expects the data to be flushed to the hard-drive after every COMMIT, so after every INSERT if no transaction is defined.
Similarly, the Jet engine was tested only on 500 rows, since it was
identified to be slow (no OleDB BULK mode is handled by now in
Now, some data.
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 results, rates and timing may vary depending on network and server load, but you get results similar to what could be expected on customer side.
For Oracle database, insertion was speed up from 476 to 53478 rows
More than a 100 x speed factor, with no code change on the client side - good plain
BatchAdd() method used, as stated above.
You can note that Jet is slow, when compared to SQlite3 or
Oracle, when it deals with insertion. And it consumes much more memory and disk
space than SQLite3.
It is also not surprising that SQlite3 in-memory tables - identified as "SQLite3 (memory)" above - are pretty fast, but that due to its asynchronous remote work, Oracle is even faster than this in-memory database! Does make sense for the amount of money to be paid for its license.
The code for reading is similar to the following (we did not include the internal assertions to enhance readability in this blog article):
// one by one retrieve values from server for i := 0 to Stat.fNumberOfElements-1 do VClient.Retrieve(Res[i],Value); // retrieve all rows using SQLite3 virtual module (via ORDER clause) Value.FillPrepare(Client,'order by RowId'); while Value.FillOne do begin end; // retrieve all rows by-passing SQLite3 virtual module (SELECT * from DB) Value.FillPrepare(Client); while Value.FillOne do begin end;
Data is therefore retrieved:
- One row per one row (using a
Retrievemethod over an ID);
- All rows at once (using a
FillPreparemethod), through the virtual table engine: the
'order by RowID'clause ensures it will pass via the SQLite3 virtual table module;
- All rows at once (also via
FillPrepare), directly accessing to the
SynDBclasses: in this release, we have added a direct request detection for a
SELECT *statement: it is up to 30-50% faster (see "All Virtual" / "All Direct" values below), for instance when you call
TSQLRecord.FillPrepare(aClient)with no where clause, to retrieve all rows at once.
Here are some speed results, run on the same hardware, over data just inserted:
If you want to use a map/reduce algorithm in your application, 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.
Note that all those tests were performed locally and in-process, via
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.
Remember also that those values refer to virtual-table external databases: for classic
TSQLRecord instance without a previous call to
VirtualTableExternalRegister (i.e. internal SQlite3
tables), it will be even faster.
Sounds like we have gone far away into optimizations for external tables,
IMHO it would be pretty hard to find a faster ORM around.
And if you compare with classic
TQuery process, you
could be amazed by the speed achieved for free with mORMot, even with
a Delphi Starter edition (the framework does not need a
Professional nor Architect version)!