Hi, following my discussion with Ani last week, I came back to SA to test the overhead of calling nested stored-procedures. In our scenario, we have close to 2000 stored-procs with some of these calls going up to 8 levels deep.

By simply doing a comparison of up to three levels, it is clear that the overhead in calling stored-procedures has a detrimental effect on performance: E.g.

Scenario #1: Routine A (simple addition) - running through a while-loop 1 million times = N milliseconds

Scenario #2: Routine A - running through a while-loop 1 million times, calling Routine B (simple addition) = 3N milliseconds

Scenario #3: Routine A - running through a while-loop 1 million times, calling Routine B which simple selects from routine C (simple addition) = 5N milliseconds

I understand that, by flattening the structure, performance will be improved, but from a high-granularity, and consistency perspective, it makes sense to place reusable code in a routine of its own.

Questions

• Is it at all possible to see what can be done to improve this scenario?
• Is there a way that our performance analytics can highlight - not the time spent within routines - but also the time spent 'between' routines?
• Is there some guidelines that dictate when 'inlining' will take place? Maybe we can structure our routines accordingly (where possible)?