[ACCEPTED]-Getting ExecuteBatch to execute faster-sybase

Accepted answer

Score: 16

To work with batches effectively you should 3 turn AutoCommit option off and turn it on 2 after executing the batch (or alternatively 1 use connection.commit() method)

connection.setAutoCommit(false);
while(rs.next())
    {
     .....
     ps.addBatch();     
    }
int[] results = ps.executeBatch();
connection.setAutoCommit(true);

Score: 10

Add ?rewriteBatchedStatements=true to the end of your JDBC url. It'll 5 give you a serious performance improvement. Note 4 that this is specific to MySql, won't have 3 any effect with any other JDBC drivers.

Eg 2 : jdbc:mysql://server:3306/db_name?rewriteBatchedStatements=true

It improved my performance by more than 1 15 times

Score: 7

I had this same problem, finally figured 16 it out though I also was not able to find 15 the right explanation anywhere.

The answer 14 is that for simple un-conditioned inserts 13 .executeBatch() should not be used. What batch mode is 12 doing is making lots of individual "insert 11 into table x ..." statements and that is 10 why it is running slow. However if the insert 9 statements were more complex, possibly with 8 conditions that affect each row differently, then 7 it might require individual insert statements 6 and a batch execution would actually be 5 useful.

An example of what works, try the 4 following which creates a single insert 3 statement as a PreparedStatement (but same 2 concept as a Statement object would require), and 1 solves the problem of running slow:

public boolean addSetOfRecords(String tableName, Set<MyObject> objects) {
    StringBuffer sql = new StringBuffer("INSERT INTO " + tableName + " VALUES (?,?,?,?)");
    for(int i=1;i<objects.size();i++) {
        sql.append(",(?,?,?,?)");
    }
    try {
        PreparedStatement p = db.getConnection().prepareStatement(sql.toString());
        int i = 1;
        for(MyObject obj : objects) {
            p.setString(i++, obj.getValue());
            p.setString(i++, obj.getType());
            p.setString(i++, obj.getId());
            p.setDate(i++, new Date(obj.getRecordDate().getTime()));
        }
        p.execute();
        p.close();
        return true;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}

Source: stackoverflow.com

[ACCEPTED]-Getting ExecuteBatch to execute faster-sybase

What is a validationQuery with respect to databases and JNDI?

How do you identify the triggers associated with a table in a sybase database?

Pivoting in Sybase SQL Query?

Is there any thing such as SELECT LAST in sql query?

*= in Sybase SQL

What is the mysterious 'timestamp' datatype in Sybase?

Sybase/ALTER TABLE: how to turn an existing column from non-nullable to nullable?

simple query: not implemented by SQLite JDBC driver

java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

right syntax to use near '?'