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

asked 21 Nov '12, 01:47

Liam's gravatar image

Liam
36191118
accept rate: 0%


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.

permanent link

answered 21 Nov '12, 04:11

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 33%

edited 21 Nov '12, 04:15

Thank you Volker. Will read this straight-away.

(21 Nov '12, 04:15) Liam
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×246
×106

question asked: 21 Nov '12, 01:47

question was seen: 1,004 times

last updated: 21 Nov '12, 04:15