i wrote the below "insertRecord" method, and it suppose to be called numerous times and insert records into data base table. when i run the code for each 500 records "which the size of the batch" it takes 20 seconds and it is very slow as that code should be run 30 times on different xml files to segregate them and insert some data into the data base table.
is there any advice how to optimize the code to achive utmost performance?
CreateTable methods
public void CreateTable(String tableName) throws SQLException, ClassNotFoundException {
if (this.isTableExists(tableName)) {
Log.i(TAG, "CreateTable", "table: ["+tableName+"] already exists.");
this.connInsert = this.getConnection();
this.connInsert.setAutoCommit(true);
this.psInsert = this.connInsert.prepareStatement("insert into "+this.TABLE_NAME+" ("+this.NODE_ID_COL+", "+this.LAT_COL+", "+this.LNG_COL+", "+this.XML_PATH_COL+") values (?, ?, ?, ?)");
} else {
Log.i(TAG, "CreateTable", "table: ["+tableName+"] does not exist, will be created");
Connection conn = this.getConnection();
Statement stmt = conn.createStatement();
stmt.executeUpdate(this.sqlTable);
stmt.close();
conn.close();
this.connInsert = this.getConnection();
this.connInsert.setAutoCommit(true);
this.psInsert = this.connInsert.prepareStatement("insert into "+this.TABLE_NAME+" ("+this.NODE_ID_COL+", "+this.LAT_COL+", "+this.LNG_COL+", "+this.XML_PATH_COL+") values (?, ?, ?, ?)");
}
}
insertRecord methods
public void insertRecord(Record rec) throws SQLException, ClassNotFoundException {
if (this.isTableExists(this.TABLE_NAME)) {
this.psInsert.setString(1, rec.getNodeID());
this.psInsert.setString(2, rec.getLat());
this.psInsert.setString(3, rec.getLng());
this.psInsert.setString(4, rec.getPath());
this.psInsert.addBatch();
if (++this.batchCnt == SysConsts.BATCH_SIZE) {
this.psInsert.executeBatch();
this.batchCnt = 0;
Log.d(TAG, "insertRecord", SysConsts.BATCH_SIZE+" records inserted.");
}
} else {
Log.e(TAG, "insertRecord", "table: ["+this.TABLE_NAME+"] does not exist");
}
}
fluch methods, to flush the remaining records in the batch
//this method should be called in the end of the code to flush the remaining records in the batch
public void flush() throws SQLException {
this.psInsert.executeBatch();
this.psInsert.close();
this.connInsert.close();
Log.d(TAG, "insertRecord", "the rest of the records flushed into data base table.");
}
Aucun commentaire:
Enregistrer un commentaire