Skip to Content

Execute batch of prepared statement in JDBC Sql taking long time during Last iteration

Hi,

I'm trying to insert large amount of rows into various tables in HANA from java application. so basically i have a single connection with which i create multiple Prepare statements. Then i prepare several inserts by setting the variables of prepare statements. Finally i execute every Prepare statement as execute Batch and close the statement.

Problem I'm facing is that, i'm doing an iteration over the above mentioned process.

I have a method writeToHANA(), which does the above mentioned operations, and this method is called inside a loop. thus the batch insertions are happening many times. Every time this insertion takes around 5 to 6 seconds to do a batch insertion in one iteration. but in the final iteration it takes a very long time about 40 seconds.

i thought this would be due to the data being written in the last iteration. Thus i also changed the data to make it lightweight but even then the problem persisted. Then i tried disabling the auto-commit and commit it manually. That didn't help either.

i also changed the number of iterations. for example,

In a loop of 20 counts ,20th loop would take a lot of time. so i changed it to run for 5 times. but this time the 5th loop ran longer. basically the last loop takes a long time to execute batch.

to check there is no memory leak, i'm also closing the statements are its execution. But i'm not able to understand why this is happening.

code snippet is provided below.


for(ArrayList<DocumentObject> batch : documentBatches){

  DBWriter.writeToHANA(batch);

  }

public static synchronized boolean writeToHANA(ArrayList<DocumentObject> batch)  {

  try {

//Prepare Statements for insertion

  String insertDocumentTableSQL = "INSERT INTO TEST.DOCUMENTS values (?,?,?,?,?,?,?,?);";
  PreparedStatement documentPreparedStatement = connection.prepareStatement(insertDocumentTableSQL);

  String insertSentencesTableSQL = "INSERT INTO TEST.SENTENCES values (?,?,?,?,?,?);";
  PreparedStatement sentencesPreparedStatement = connection.prepareStatement(insertSentencesTableSQL);


//Preparing multiple inserts to the prepare statements
for(DocumentObject docObj : batch){



documentPreparedStatement = prepareInsertsToDocumentsTable(docObj,documentPreparedStatement);           //sets the '?' in the query with actual values and adds it to the batch

sentencesPreparedStatement = prepareInsertsToSentencesTable(headlineSRLDocument, sectionId,sentencesPreparedStatement);

    }


//execute the preparestatements
  connection.setAutoCommit(false);
long start = System.currentTimeMillis();
  documentPreparedStatement.executeBatch();
  sentencesPreparedStatement.executeBatch();

     connection.commit();

  documentPreparedStatement.close();
  sentencesPreparedStatement.close();
System.out.println("time taken for Execute Statements: "+(System.currentTimeMillis()-start)/1000+" seconds" );

return true;

}


Below is the console output that i had got to track the time taken for every iteration.

time taken for Execute Statements: 3 seconds
time taken for Execute Statements: 4 seconds
time taken for Execute Statements: 4 seconds
time taken for Execute Statements: 4 seconds
time taken for Execute Statements: 5 seconds
time taken for Execute Statements: 5 seconds
time taken for Execute Statements: 9 seconds
time taken for Execute Statements: 10 seconds
time taken for Execute Statements: 6 seconds
time taken for Execute Statements: 47 seconds

Could someone tell me why the last loop is taking so much time to execute?

Thank You.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Jul 25, 2016 at 04:24 PM

    Hello

    One thing you haven't shown is the number of objects in each batch. Is it possible that there are more in the last batch?

    Michael

    Add a comment
    10|10000 characters needed characters exceeded

    • Glad you worked out the cause of the inconsistent behaviour.

      Once you do start to bulk insert large amounts of data into HANA, for increased throughput you might start to consider multiple concurrent connections (if this is possible due to data dependencies, although if you are using foreign key definitions in HANA you'd be first I've come across! 🤪). Controlling the delta merge process and table partitioning/distribution can also have a dramatic effect on insert performance.

      Michael

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.