118 SQL Statements were executed for one transaction


(Jiachuan Li) #1

Hi Team, 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.

  1. Whether these so much SQL execution are reasonable for a transaction, and these must needed to happen to complete one transaction?
  2. 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.

(Li) #2

quite agree with that. this test report same as devmode=false.


(Jiachuan Li) #3

Hi Team, Happy New Year!

I would like to get your feedback about this question, could you please help? Thanks.


(Mike Hearn) #4

Hello, happy new year!

Corda open source isn’t performance optimised (at all). So it is not surprising to hear there are over 100 queries being done for your use case.

We have a dedicated performance team at this time but the performance improvements are mostly going into the R3 Corda edition (i.e. the Enterprise edition). We expect to have very good performance numbers to report here but at the moment we’re still optimising. To what extent we also optimise the open source codebase is currently undecided.

If you are interested in getting access to R3 Corda builds when they become available please do get in touch with partner@r3.com

Hope that helps!