{"id":3986,"date":"2011-07-05T14:12:37","date_gmt":"2011-07-05T08:42:37","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=3986"},"modified":"2012-09-23T18:04:32","modified_gmt":"2012-09-23T12:34:32","slug":"batch-update-performance-enhancements-using-sql-withbatch","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/batch-update-performance-enhancements-using-sql-withbatch\/","title":{"rendered":"Batch update performance enhancements using SQL withBatch()"},"content":{"rendered":"

Hi guys,
\n
\nRecently as part of a project, I had to populate a SQLite database with large amounts of data pertaining to a number of classes requiring more than 5000 inserts and updates per class. I created a new SQLite database using Groovy’s Sql class. The initial strategy involved creating prepared statements and executing individual insert\/update statements for each record that needed to be inserted\/updated in the new SQLite database.
\n
\nHowever the process was taking longer than expected, and the cumulative time taken for the whole application to sync less than 30 classes was coming to be more than 2 minutes. This time taken was extremely high regarding the context of the application and was a real performance bottleneck. What I did notice was that the insert statements were identical for a particular class, disregarding the values that needed to be inserted. The same was the case with the update statements.
\n
\nAfter looking through the Sql GDK, I found a method named Sql.withBatch()<\/em> that performs batch manipulation of records in a database. See the following code for illustration:
\n
\n[code]
\nSql sql = Sql.newInstance("jdbc:sqlite:\/home\/ron\/Desktop\/test.db", "org.sqlite.JDBC")
\nsql.execute("create table dummyTable(number)")
\nLong startTime = System.currentTimeMillis()
\n100.times {
\n sql.execute("insert into dummyTable(number) values(${it})")
\n}
\nLong endTime = System.currentTimeMillis()<\/p>\n

println "Time taken: " + ((endTime – startTime)\/1000)
\n[\/code]
\n
\nThe output of the above code comes out to be 14.313 seconds. That is to execute 100 insert statements with only a single attribute, it would take around 15 seconds. The time taken to insert records is dependent on the number of records being inserted and increases exponentially. Clearly a performance bottleneck in any application involving batch inserts and updates.
\n
\nLet us consider the same code and the performance with the withBatch()<\/em> closure.
\n[code]
\nSql sql = Sql.newInstance("jdbc:sqlite:\/home\/ron\/Desktop\/test.db", "org.sqlite.JDBC")
\nsql.execute("create table dummyTable(number)")
\nLong startTime = System.currentTimeMillis()
\nsql.withBatch {stmt->
\n 100.times {
\n stmt.addBatch("insert into dummyTable(number) values(${it})")
\n }
\n stmt.executeBatch()
\n}
\nLong endTime = System.currentTimeMillis()
\nprintln "Time taken: " + ((endTime – startTime)\/1000)
\n[\/code]
\n
\nThe time taken with the above code comes out to be 0.103 seconds! A remarkable performance improvement over the conventional method of inserting records using the execute() method.
\n
\nThe only drawback with using the withBatch()<\/em> closure is that it does not allow prepared statements to be added to the batch. This limits the use of batch statements as we have to manually create insert or update statements.
<\/p>\n","protected":false},"excerpt":{"rendered":"

Hi guys, Recently as part of a project, I had to populate a SQLite database with large amounts of data pertaining to a number of classes requiring more than 5000 inserts and updates per class. I created a new SQLite database using Groovy’s Sql class. The initial strategy involved creating prepared statements and executing individual […]<\/p>\n","protected":false},"author":33,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":22},"categories":[7],"tags":[609,610,558,608],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/3986"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/33"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=3986"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/3986\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=3986"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=3986"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=3986"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}