CODEWITHSUNDEEP
performanceoracle-databasestored-proceduresplsql
jDeveloper
jDeveloper

Reputation: 2126

Oracle/PLSQL performance

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

For example:

mainSP
  callSP1
  callSP2
  callSP3
end;

rather than:

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

Upvotes: 2

Views: 457

Answers (4)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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!

Upvotes: 6

Gary Myers
Gary Myers

Reputation: 35401

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.

Upvotes: 6

Jeff
Jeff

Reputation: 5963

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.

Upvotes: 2

KM.
KM.

Reputation: 103589

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.

Upvotes: 0

Related Questions