Groovy SQL and batch updates (microbenchmark)

Before saying that a tool is not appropriate for a particular task, you should learn as much as possible about it.

The following tests were performed using the snapshot of version 1.8.1 of Groovy, which includes the improvement GROOVY-4798. When analyzing the results, keep in mind that:

  • Microbenchmarks are just microbenchmarks;
  • The values should be compared with each other and not considered in absolute terms;
  • The corresponding servers of different RDMBS were at distinct geographic locations, making it impossible to compare values ​​that do not belong to the same group/RDBMS: while the PostgreSQL server was on the same computer on which the tests were run, the Oracle server was several miles away.


OS: x86 Windows XP 5.1
JVM: Sun Microsystems Inc. 1.6.0_23

=============================
== Testing oracle10g ==
=============================
== without batch (Statement)
1385 of 1385 rows inserted in 24375 (ms)
== without batch (PreparedStatement)
1385 of 1385 rows inserted in 22141 (ms)
== with batch (Statement)
1385 of 1385 rows inserted in 22312 (ms)
== with batch (PreparedStatement)
1385 of 1385 rows inserted in 140 (ms)
=============================
== Testing postgresql84 ==
=============================
== without batch (Statement)
1385 of 1385 rows inserted in 1156 (ms)
== without batch (PreparedStatement)
1385 of 1385 rows inserted in 1203 (ms)
== with batch (Statement)
1385 of 1385 rows inserted in 234 (ms)
== with batch (PreparedStatement)
1385 of 1385 rows inserted in 141 (ms)
=============================
== Testing hsqldb21 ==
=============================
== without batch (Statement)
1385 of 1385 rows inserted in 250 (ms)
== without batch (PreparedStatement)
1385 of 1385 rows inserted in 250 (ms)
== with batch (Statement)
1385 of 1385 rows inserted in 93 (ms)
== with batch (PreparedStatement)
1385 of 1385 rows inserted in 32 (ms)


The highlighted parts of the code are:


def updateActions = [
  'without batch (Statement)': {sql, reader ->
     // (...)
     while ((r = reader.readNext()) != null && r.length > 0) { /* (...) */
       sql.executeUpdate("insert into city(geo_world_map_id, code, short_code, latitude, longitude) values ("
+ "${r[0]}, '${code}', '${r[8]}', ${r[4]}, ${r[5]})") /* (...) */
     } /* (...) */
  },

  'without batch (PreparedStatement)': {sql, reader ->
     // (...)
     while ((r = reader.readNext()) != null && r.length > 0) { /* (...) */
        sql.executeUpdate('insert into city(geo_world_map_id, code, short_code, latitude, longitude) values ('
+  '?, ?, ?, ?, ?)', r1) /* (...) */
     } /* (...) */
  },

  'with batch (Statement)': {sql, reader ->
     sql.withBatch(batchSize) {stmt ->
        // (...)
        while ((r = reader.readNext()) != null && r.length > 0) { /* (...) */
           stmt.addBatch("insert into city(geo_world_map_id, code, short_code, latitude, longitude) values ("
+ "${r[0]}, '${code}', '${r[8]}', ${r[4]}, ${r[5]})") /* (...) */
        }
     }
  },

  'with batch (PreparedStatement)': {sql, reader ->
     sql.withBatch(batchSize, 'insert into city(geo_world_map_id, code, short_code, latitude, longitude) values ('
+ '?, ?, ?, ?, ?)') {stmt ->
        // (...)
        while ((r = reader.readNext()) != null && r.length > 0) { /* (...) */
           stmt.addBatch(r1) /* (...) */
        }
     }
  }
]

Some more results


OS: x86 Windows XP 5.1
JVM: Sun Microsystems Inc. 1.6.0_23
=============================
== Testing hsqldb21 ==
=============================
== without batch (Statement)
37230 of 37230 rows inserted in 2610 (ms)
== without batch (PreparedStatement)
37230 of 37230 rows inserted in 2750 (ms)
== with batch (Statement)
37230 of 37230 rows inserted in 2047 (ms)
== with batch (PreparedStatement)
37230 of 37230 rows inserted in 953 (ms)
=============================
== Testing postgresql84 ==
=============================
== without batch (Statement)
37230 of 37230 rows inserted in 26672 (ms)
== without batch (PreparedStatement)
37230 of 37230 rows inserted in 28500 (ms)
== with batch (Statement)
37230 of 37230 rows inserted in 6031 (ms)
== with batch (PreparedStatement)
37230 of 37230 rows inserted in 4406 (ms)

Groovy project used during tests

Additional References

2 Responses to Groovy SQL and batch updates (microbenchmark)

    • Oi, Wanderson. Tudo bem? Acho que você quis dizer que usando o GPars o desempenho pode melhorar mais um terço, certo? Ou seja, o tempo total de execução cair 1/3.
      Ainda não testei. Está na minha TODO list.

      Obrigado.

      []‘s.

Leave a Reply

Your email address will not be published. Required fields are marked *

*


seven − = 2

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>