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.