[GROOVY-5355] Oracle withBatch inserts is slow

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Cannot Reproduce
• Affects Version/s: 1.8.6
• Component/s: SQL processing
• Environment: Groovy script on a Solaris server, Oracle 10g server on the same subnet.
• Assignee: Pascal Schumacher
• Reporter: Edmond Wong
• Created: 08/Mar/12 16:01
• Updated: 01/Jun/13 06:11
• Resolved: 03/May/13 16:02

Description

I did some performance test with two sql inserts file, both files have the same 10k records. The first file I have manually put a COMMIT statement after 1000 records. Then I took the first file and execute by "time sqlplus user/pass@db @1st_inserts.sql > /dev/null", the time sqlplus finish loading all the records is 13 seconds.

On the 2nd file, I run it by my groovy script like:

c = 1
ticks = System.currentTimeMillis()

  to_db.withBatch(1000)
  {
    stmt ->
    new File("2nd_inserts.sql).eachLine
    { line ->
      stmt.addBatch(line)
      c++
    }
  }

ticks = System.currentTimeMillis() - ticks

rec_count = (c-1)

out << "\n$rec_count rows imported, elapsed ${ticks}ms\n"

Using my groovy script to load the same 10k records, it took 28 seconds. What do I need to do to improve the batch inserts performance. I am tryng to avoid calling sqlplus within my Groovy script.

Thanks,

Edmond