Oracle/PLSQL performance

Question

Is there any difference in performance when you break down a stored procedure into multiple procedures instead of having a big procedure?!
wich one is faster?!
for example:

mainSP
  callSP1
  callSP2
  callSP3
end;

rather than

SP
....
.....
....

Thanks guys.

Answer 1

it depends on what is happening.

if mainSP is looping over callSP1 and mainSP could just do single statements to work on a set of data then it would be slower.

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

if you are duplicating code all over, make a common routine. other wise keep it together.

Answer 2

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

In general it should not matter. But when in doubt test.

Answer 3

In 10g and above, there is an 'optimizing compiler'.

In 11g, it will do "subprogram inlining. Subprogram inlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram"

I thought the inlining was part of 10g, but can't find it documented there.

Answer 4

Any performance benefit would be in very rare cases, e.g. where the main proc is being called many many times in a loop and each individual iteration does not take much time.

Most of the time the maintainability of having your program broken down into logical steps will far outweigh the minor performance gain that might be had.

As has been stated before, benchmark and test - unless you see a significant benefit, go for maintainability - future developers will thank you!