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.
asked 21 Nov '12, 01:47
Well, if you have discussed with Ani, she will know best - so I'll just give a few hints:
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...
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: