4

The code should be database independent so I am using normal sql.

My query can change at runtime say table1 changed to table2. But lets assume for table1 query I have to update 2000 records with slot of 50, means as soon as 50 records are processed I will commit.

I have two approaches for setting values in IN clause of sql statement. I want to know which one of the below code is more efficient (priority is optimization and maintainability is secondary)?

PreparedStatement preparedStatement = sqlObj.prepareStatement(
    "UPDATE table1 column1=? WHERE table_id IN (" + StringUtils.repeat("?,", paramArray.length-1)+ "?)");
preparedStatement.setInt(1, 0);
for(int idx = 0; idx < paramArray.length; idx++) {
    preparedStatement.setInt(idx+2, paramArray[idx]);
}
preparedStatement.executeQuery();

Or

PreparedStatement preparedStatement = sqlObj.prepareStatement(
    "UPDATE table1 column1=? WHERE table_id IN (?)");
for(int idx = 0; idx < paramArray.length; idx++) {
    preparedStatement.setInt(1, 0);
    preparedStatement.setInt(2, paramArray[idx]);

    preparedStatement.addBatch();
}
preparedStatement.executeBatch();

Edit: Lets say param.length is 50 and this whole code exectutes 40 times i.e. processing 2000 records.

So in first case it will append 50 ? and then set variable for them making one update query and in second case it will create batch of 50 update queries.

Naman Gala
  • 4,670
  • 1
  • 21
  • 55
  • If you're just calling the code once, or rarely, then the first one. Otherwise it depends on how often you make the same update, how many parameters you include each time, and the statement caching of your database. – khelwood Nov 12 '14 at 12:00

3 Answers3

0

It seems like in second you don't need IN at all, you can use WHERE table_id = ? because you set 1 value each time. In this case second will be faster because you will use Batch.

Please, see Why are batch inserts/updates faster? How do batch updates work?

If you are using Oracle, you can also do something like

create or replace TYPE "NUMBER_ARRAY" IS TABLE OF NUMBER(18,0);

and in Java code

PreparedStatement preparedStatement = sqlObj.prepareStatement(
"UPDATE table1 column1=? WHERE table_id IN (SELECT column_value FROM TABLE (CAST(? AS number_array)))");    
JdbcUtils.setArray(2, tableIds.toArray(new Long[tableIds.size()]), ps);
Community
  • 1
  • 1
prsmax
  • 223
  • 1
  • 7
0

The first approach leads to recreating the prepared statement for every call, so there is no need for a prepared statement. The second can be precompiled and reused. That's the main difference: Your second approach allows you for reusing the query object and omitt the query parsing. EDITED: Sorry, read too fast. Binding many parameters obviously is more inefficient than binding one.

Peter
  • 1,769
  • 1
  • 14
  • 18
  • Can you please clarify bit on this "so `there is no need for a prepared statement`", what should I use in place of prepared Statement. – Naman Gala Nov 13 '14 at 05:27
  • No, I just read to fast and incomplete. See the "EDITED" - part of the response. It is really up to that your second approach states that it are only two parameters you want to bind, so the main difference between your statements is the batch processing. This is not really needed as you are sending only one command (sql query). – Peter Nov 13 '14 at 13:13
  • You are trying to say that binding 50 variables are inefficient then setting batch of 50 sql update statement.. Is my understanding correct about your answer? – Naman Gala Nov 13 '14 at 16:27
0

If this is just a question out of curiosity I'd say it depends more on the database than in the prepared statement. Database operations are way more expensive than moving some bytes around.

If you are asking that question because you want to make your application faster I'd have to ask whether this update is really a performance bottleneck. Have you measured it?

If this is not a performance bottleneck, and you want to optimize just in case: don't. It is way more important to have maintainable code than to optimize uncritical code. Use the variant that has the least complexity and is easiest to understand: variant 2.

Thomas Stets
  • 3,015
  • 4
  • 17
  • 29
  • I really appreciate the way you answered. Above code is sample code but it represent code from my application. For now my priority is optimization and maintainability is secondary.. – Naman Gala Nov 13 '14 at 03:50