1

I have multiple stored procedures which are on my DB example: proc1, proc2, ...proc10.

I want to call them in a loop from java. What is the best way to do this to avoid multiple network trips, repeated compiling, performance ?

This is what I have now: 

for (int i = 1; i <= 10; i++) 
{
  CallableStatement stmt = conn.prepareCall("call proc"+i);
  stmt.execute()
}

I don't want to execute a single top level procedure calling these 10 procedures as I want more control of these executions from my code. For example, I might get an interrupt after I execute 5 procedures and may have to context switch. I can't do this when I execute a top level procedure on the server side

Bolimera Hannah
  • 195
  • 1
  • 2
  • 11
  • 1
    What makes you look for another solution than the one you have? For your given constraints, it looks mostly reasonable. If these calls happen very often, you might want to keep the `CallableStatement`s in an array, though. – Erich Kitzmueller Apr 24 '17 at 12:01

1 Answers1

2

One way would be to write another stored procedure that calls proc1 through proc10 in order. Calling the "uber-procedure" should be a single round-trip.

Reference:

I [...] had to rule out that option as I want more control of these executions from my code.

If you need control from your code, then there is pretty much no alternative to what you are currently doing. Control in your code, implies that your code must execute between each of the calls to the stored procedures. That in turn implies (at least) a round trip between each call.

(I suppose, you could write, or maybe generate a different uber-procedure for each of your scenarios. But that's basically the same as my suggestion.)

Community
  • 1
  • 1
Stephen C
  • 698,415
  • 94
  • 811
  • 1,216
  • Thanks. I thought about it but had to rule out that option as I want more control of these executions from my code. For example, I might get an interrupt after I execute 5 procedures and may have to context switch. I can't do this when I execute a top level procedure on the server side. – Bolimera Hannah Apr 24 '17 at 10:22
  • Thanks again. One final question, would batch executes in anyway help in what I am trying to achieve ? Would I have control to stop in between ? – Bolimera Hannah Apr 24 '17 at 12:44
  • No you wouldn't. Read the javadoc for executeBatch. – Stephen C Apr 24 '17 at 13:10