mercredi 12 août 2015

how to optimize the insert statement to achive utmost performance

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