Chunk Database Update or Deletion

Updating or deleting large amounts of data may require to split the operation in smaller chunks. This because the large operation may cause disk or memory issues and may lead to database locks.

  • The update example is using a nested loop with a Postgres.setResultSetHoldability.
  • The delete example is loading id values into an ArrayList.
groovy
import java.sql.ResultSet
sql.setResultSetHoldability( ResultSet.HOLD_CURSORS_OVER_COMMIT)
def i = 0
sql.eachRow( "SELECT userid FROM dom_user_ext_6089".toString() ){ r ->
  sql.execute("UPDATE dom_user_ext_6089 SET ext_string_015='T' WHERE userid=?".toString(), [ r.userid ])
  i++
  if ( i > 1000 ){
    sql.commit()
    i = 0
    print "."
  }
}
sql.commit()
Deletion example:
groovy
  List ids = new ArrayList()
  sql.eachRow( 'SELECT id FROM large_table' ) { m ->
    ids.add( m.id )
  }
  int cnt = 0;
  for ( int id : ids ) {
    sql.execute('DELETE FROM large_table WHERE id=?', [id] )
    cnt++
    if ( cnt > 1000) {
      print '.'
      sql.commit()
      cnt = 0;
    }
  }
/

Navigation