0

I have a service that updates multiple tables through multiple services with in a transaction boundary, if one of them fails everything need to be rolled back. One of these SQL update has around 2k+ records to update and its done in two batches 1000 records at a time. Problem is this update is taking too long around 2mins sometimes and transaction timing out. Is there a way in this sql can be performed spanning multiple threads each thread updating 100 records. Thanks in advance.

astentx
  • 6,393
  • 2
  • 16
  • 25
SBB
  • 17
  • 1
  • 8
  • What database? Is this with jdbc? – Nathan Hughes Jul 16 '21 at 20:01
  • DB is Oracle, yes jdbc – SBB Jul 16 '21 at 20:17
  • 2
    Jdbc is horribly slow. SQL*Loader will load a couple thousand rows in a few seconds. – Nathan Hughes Jul 16 '21 at 20:24
  • @NathanHughes the question was around updates, not inserts; so SQL Loader is not appropriate – BobC Jul 17 '21 at 02:47
  • That’s true. Though you could insert into a work table and execute a merge from there to the table to update ? – Nathan Hughes Jul 17 '21 at 05:30
  • Whatever the method, the goal should be to execute a _single_ SQL update command, if possible, rather than iterate through a loop. Executing iterative SQL statements in for-next loops will _always_ be slow and generally make a rollback impossible if executed across multiple threads. If necessary, build a PL/SQL API procedure to handle multiple SQL commands (still try to avoid loops) and make a single call from the app to the procedure; this will allow you to better manage parallelization, transactions/rollbacks, and error handling in general over trying to do everything in Java. – pmdba Jul 17 '21 at 14:50
  • Its all legacy code, for now we have managed it with running independent updates in separate threads. It improved the performance somewhat, at least not timing out. Thanks all – SBB Sep 25 '21 at 03:13

0 Answers0