JDBC batch insert
Hi, I'm looking for a way to implement the JDBC pattern "executeBatch" in ObjectScript: https://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm
Since Ensemble 2017.2.2 there is a new method called executeParametersBatch : https://docs.intersystems.com/ens20172/csp/docbook/relnotes/index.html#D...
Even in the Ensemble 2018.1 change log, mention a patch on this method: https://docs.intersystems.com/ens20181/csp/docbook/relnotes/index.html#J...
Do you have any examples or tracks to use the executeParametersBatch method of %Net.Remote.Java.JDBCGateway?
Otherwise have you found other workaround for batch insert in JDBC through an EnsLib.SQL.OutboundAdapter?
Thank you in advance for your answers.
Hi Guillaume,
I'm not sure what you're trying to get at. Our core JDBC driver supports batch processing through exactly the mechanism described in the tutorial you referenced, so that should work fine using default JDBC methods on the Java side. The JDBC SQL Adapter in EnsLib on the other hand was designed for a message-by-message processing and therefore the Adapter doesn't expose a batching mechanism.
Maybe you can share a little more on the actual use case you're implementing? Buffering up messages for batch insertion or does the message carry a lot of data that deserves a batch insert by itself? Or am I totally on the wrong track here? :-)
Thanks,
benjamin
Hi Benjamin,
In some cases we have to use Ensemble as an ETL not an ESB.
So we extract lot of data, transform and load them throw the JDBC SQL Adapter in EnsLib. To do so, we dont do it line by line throw messages but ResultSet by ResultSet (one ResultSet can have more than 500 000 lines and 30 colones). This pattern work well.
But we have start to have time treatment issue. Our process took more than 8 hours. When we analyze it, what it cost time is the select and insert treatment.
We solved the problem of select treatment with this post : https://community.intersystems.com/post/jdbc-large-query-optimisation
So now, we are looking for a way to improve insert time. Our guess is to implement throw JDBC SQL Adapter in EnsLib the java pattern with PrepareStatement.addBatch() then executeBatch().
Do you have any idea to improve the insert treatment ?
Thanks Marc,
Your code work great.
We gain a factor ten with this implementation.
Below our current implementation.
Hi Guillaume,
Here's some rough code showing how to use executeParametersBatch. I put this together from a few other working examples I had but I haven't tested this actual code and it may have bugs.
As always, this is provided as sample code only and is not meant for production use :)
-Marc