Why update takes long time but not insert in spring jdbctemplate batchupdate?

Question

I am trying to do a batch update for 3000 records. It takes around 15 seconds to complete, but insertion happens within 1 second.

Had included rewriteBatchedStatements=true also. Do i need to do anything else?

Below is my code.(not included exception handling and success and failure count/ids portion. They are the same for both update and insert).

Technologies used : spring,jdbctemplate,mysql,java,intellij idea

  result = jdbcTemplate.batchUpdate(
                //"insert into xxx(aaa,bbb,item_code) values(?,?,?)",
                        "update ignore xxx set aaa = ?, bbb= ? where item_code = ?",
                        new BatchPreparedStatementSetter() {
                    public void setValues(PreparedStatement ps,int i) throws SQLException {
                        ps.setDouble(1, Double.parseDouble(new JSONObject(jsonArray.get(i).toString()).get("aaa").toString()));
                        ps.setDouble(2, Double.parseDouble(new JSONObject(jsonArray.get(i).toString()).get("bbb").toString()));
                        ps.setString(3, new JSONObject(jsonArray.get(i).toString()).get("code").toString());
                    }

                    public int getBatchSize() {
                        return jsonArray.length();
                    }
                } );

Do you have an index on item_code in DB? If not, you could try creating one. — Vasan, May 06 '18 at 05:26

score 1 · Answer 1 · answered May 06 '18 at 18:41

1

Also include useServerPrepStmts=false .

Similar question was asked here: JDBC batch insert performance

Jdbc Performance improvement tips: http://javarevisited.blogspot.in/2012/01/improve-performance-java-database.html

answered May 06 '18 at 18:41

mindSet

231
2
10

Why update takes long time but not insert in spring jdbctemplate batchupdate?

1 Answers1