[SOLVED] JMeter Groovy SQL Batch Update very slow

JMeter Groovy SQL Batch Update very slow

Hi I am trying to update multiple MYSQL Tables from data set in DB. I am updating 3 tables using batch if I update a single row it works fine and update records in all 3 tables within 1-2seconds but if I fetch 3 records and update then suddenly it can take between 13-25 seconds. I have tried quite a lot of things somehow just not managed to get it working faster

Here's my code in JSR223 PostProcessor

import groovy.sql.Sql;
import groovy.sql.*
import java.sql.ResultSet;
def dburl = 'jdbc:mysql://${__P(${env}_host)}:${__P(${env}_port)}/${__P(${env}_db)}?verifyServerCertificate=false&requireSSL=false&useSSL=false&useServerPrepStmts=false&rewriteBatchedStatements=true'
def user = '${__P(${env}_username)}'
def password = '${__P(${env}_password)}'
def driver = '${__P(${env}_driver)}'

def sql = Sql.newInstance(dburl, user, password, driver)
sql.connection.autoCommit = false
if(vars.getObject("resultSet").size() != 0) {
    
ArrayList results = vars.getObject("resultSet");
sql.withBatch 
      { stmt ->
          results.each 
      { 
      rs ->
             stmt.addBatch("""UPDATE Table1 SET column1 = '${rs.column1}' 
             WHERE column2 = ${rs.column2}""")
             stmt.addBatch("""UPDATE Table2 SET column1 = '${rs.column1}' 
             WHERE column2 = ${rs.column2}""")
             stmt.addBatch("""UPDATE Table3 SET column1 = '${rs.column1}' 
             WHERE column2 = ${rs.column2}""")

          }
          stmt.executeBatch();        
}
log.info("End")
}
sql.commit()

Thanks

Solution

Thanks all. I know the DB Table structure is not the best but this is my internal requirement to have it like this.

I managed to improve the performance by tweaking couple of my.ini settings

Mainly innodb_buffer_pool_size=2G i don't remember what it was before but after changing to 2G performance improved massively.

Thanks