Deep level calling of Stored Procedures - Expensive

[Former Member]

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)?

[VolkerBarth]

Well, if you have discussed with Ani, she will know best - so I'll just give a few hints:

Is it at all possible to see what can be done to improve this scenario?

What exactly do you want to improve? - It's more than common that abstraction/modularity come at a price - so you will have to make your choices whether their value is more important than performance (which may have its disadvantage in terms of maintenance effort). - Turning a simple addition into an UDF is apparently not a good choice, but I'm sure your procedures are way more complex...

Is there some guidelines that dictate when 'inlining' will take place? Maybe we can structure our routines accordingly (where possible)?

Cf. pages 16/17 from this excellent whitepaper by Ani: They deal with the structure of funtions and procedures that can be inlined:

Query Processing Based on SQL Anywhere 12.0.1 Architecture as referenced in this doc page.

To cite that paper for those functions who are never inlined:

Temporary functions, recursive functions, and functions with the NOT DETERMINISTIC clause are never inlined. In addition, a function is never inlined if it is called with a subquery as an argument, or when it is called from inside a temporary procedure.

[Former Member]

Thank you Volker. Will read this straight-away.