118 SQL Statements were executed for one transaction

Question

recently we are trouble shooting one performance problem for our application, the code initially was build based on M13 version of Corda tutorial code and we followed Corda’s releases and now it is updated as V2.0. The business is simple, Party A upload a contract document with some meta data in a form, then send this transaction to Party B, we defined some simple conditions in verify function, so normally the transaction will be completed without any manual action. But this process if we did that in our local environment, it took around 3 secondes(with one 2.9M attachment), but when we deploy it to our dev environment which H2 is hosted in a seperate server from the CorDapp, it always take 15-20 seconds to complete, with one notary node.

We tried to enable the H2 track log feature, and from the log, we found that 165 SQL statements were executed, includes 114 selects, 31 inserts, 16 deletes, 2 updates and 2 alters. Our flow is mostly similar as the tutorial’s code, except the acceptor flow we have the similar verify function as the initiator flow and we have attachment but the tutorial doesn’t.

Use the same approach, I executed one create IOU transaction on the Corda Example code which is based on V1.0(as there is no V2.0 example code, so I only can do it on V1.0), for that transaction, 118 SQL Statements were executed, includes 74 selects, 28 insert, 14 deletes and 2 updates.

There are also lots of “SET LOCK_MODE” and COMMITs, and checkpoints were delete and inserted frequently. So we would like to get your comments for below questions, kindly help on this. Thanks.

Whether these so much SQL execution are reasonable for a transaction, and these must needed to happen to complete one transaction? As we may not be able to understand what is the purpose for each SQL execution, so do you have any suggestion about what we should do for next step to get the root cause for it? Is 15-20 seconds for a transaction is normal as we host H2 database and notary separately in different servers? Our CorDapp(Party A, Party B and notary), H2 database are hosted on Azure VM separately.

Answer 1

Work on Corda has focussed thus far more on functionality than on performance. There are many performance improvements - in the database and elsewhere - that will be implemented in future releases.

However, there is no reason for a transaction to take 15-20 seconds.

How many times are you running the transaction? I ask because every time a node sees a new attachment as part of a transaction, it caches it and stores it for later reference. This means that the large attachment only needs to be sent across the wire for the first transaction. If you send 10 transactions referencing the same attachment, it will only be downloaded the first time, and the other 9 times will be much faster. Do you observe these improvements?