sql.withBatch(batchSize, query) {} does not give performance benefit. Inserts rows one by one in database

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

sql.withBatch(batchSize, query) {} does not give performance benefit. Inserts rows one by one in database

adithyank
This post was updated on .
Team,

sql.withBatch(batchSize, query) {} does not give performance benefit.
Inserts rows one by one in database

Below is the code I used

```
sql.withBatch(1000, phQuery) {BatchingPreparedStatementWrapper ps ->
                                        rows.each {
                                                ps.addBatch(it)
                                        }
                                }
```

The observations are below

1. The time taken to insert 10 lac rows is same as individual insert
statements that I was having previously
2. Also, in Database, the row count increases one by one and not in
multiples of 10000

I was expecting that 'Sql' class will commit the rows once for given batch
size. But, it did not happen that way. My expectation may be wrong !

So, I changed the code as below

```
int count = 0
Connection con = sql.connection
con.setAutoCommit(false)
def stmt = con.prepareStatement(phQuery)

rowValues.each {

        Object[] row = it

        row.eachWithIndex {o, i -> stmt.setObject(i + 1, o)}

        stmt.addBatch()
       
        count++

        if (count == batchSize)
        {
                stmt.executeBatch()
                con.commit()
                count = 0
        }
}

//to commit the balance rows
stmt.executeBatch()
con.commit()

```

This code gave expected my required performance. But, it is java way, not
groovyish way !!!

Is it wrong to expect this performance level from `sql.withBatch(batchSize,
query) {}` itself?



--
Sent from: http://groovy.329449.n5.nabble.com/Groovy-Dev-f372993.html
Reply | Threaded
Open this post in threaded view
|

Re: sql.withBatch(batchSize, query) {} does not give performance benefit. Inserts rows one by one in database

adithyank
Sorry, the embedded code inside the  tags disappeared in the final mail. So,
edited the original post with with  tags...!



--
Sent from: http://groovy.329449.n5.nabble.com/Groovy-Dev-f372993.html