java - Which of the two JDBC query batching approaches is faster? -

i trying run update statements in multiple batches using rownum on table has millions of records.

the first approach batch queries , running executebatch() method below,

for (i = num; < limit; += num) {     string query = "update table set somecolumn ='t' rownum<=" + i;     preparedstatement = dbconnection.preparestatement(query);     preparedstatement.addbatch(); } preparedstatement.executebatch(); dbconnection.commit(); 

second approach run 1 batch update statement , committing after every batch shown below,

string query = "update table set somecolumn ='t' rownum<=?"; preparedstatement = dbconnection.preparestatement(query); int count = 1; while (count > 0) {     preparedstatement.setint(1, num);     count = preparedstatement.executeupdate();     dbconnection.commit(); } 

i thought second approach cleaner since commits after every batch, first approach taking lesser time second approach.

can explain me why so? or if there's mistake in approaches , understanding.

can explain me why so?

there number of different things can optimized here:

1. the work done jdbc/odbc driver , db engine prepare , parse statement
2. the round-trip traffic between client , db server
3. the work done db engine open , close (i.e. commit or rollback) transaction

you examples optimizing different things:

• by using bound parameter (as in second example) prepare statement once, reduces work done odbc/jdbc driver , database engine well.
• by executing batch less (1st example), reducing number of round trips between client , server.
• by committing less (1st example again) reducing number of times database needs open , close transaction.

as found, bottleneck turns out overhead opening , closing transactions. multiple round trips aren't helping either. more costly not using bound parameter.

happily, in example can optimize 3 things. can use bound parameter, send commands database in 1 go, , execute single commit. see answer jean de lavarene.

do note change in behavior though: if commit in single batch, 1 error cause in batch roll back. may want, if not, such considerations may take precedence on performance.