Have Geek Brain

Oracle / PLSQL Performance

Is there a performance difference when splitting a stored procedure into multiple procedures instead of a large procedure?

Which one is faster?

For instance:

mainSP
  callSP1
  callSP2
  callSP3
end;

but not:

SP
  .... 
  ....
  ....
+1


a source to share


4 answers


Any performance advantage will be in very rare cases, for example. where the main proc is called many times in a loop, and each individual iteration doesn't take long.

In most cases, the maintainability of your program, broken down into logical steps, far outweighs the slight performance gain that can be achieved.



As said, tests and benchmarks - if you don't see significant benefit, go for maintainability - future developers will thank you!

+6


a source


At 10g and up, there is an "optimizing compiler".

In 11g, a "subroutine" will run. A subroutine substring replaces a subroutine call (to a subroutine in the same program block) with a copy of the called subroutine "



I thought the inlay was part of 10g, but cannot find it documented there.

+6


a source


In theory, there is a slight performance improvement each time a stored procedure is called from another stored procedure. However, the exact impact depends on the number of parameters and the type of stored procedure being called.

In general, it doesn't matter. But with questionable testing.

+2


a source


it depends on what's going on.

if mainSP is looping over the call to SP1, and mainSP can simply execute separate instructions to operate on the dataset, then it will be slower.

there is a little overhead in calling other procedures, and also a little pain in passing error messages.

if you are duplicating code all over, follow the normal procedure. others keep it together intelligently.

0


a source






More articles:


All Articles