How does SQL Anywhere pass parameters?

[Former Member]

Per Breck's suggestions, I am adding another question directly about how SQLA passes parameters. In the past couple years the question has come up several times when trying to move inline code to a stored procedure that now requires passing parameters.

I did some testing and here are my results. My test consisted of a stored procedure that added the 1st 2 parameters together and sent them back as an out variable. Variables include IN or INOUT parameter types and integer or varchar parameters. Results are in seconds for calling the procedure 1,000,000 times.

• 2 Params (Ints, In) - 3.2
• 2 Params (Ints, InOut) - 4.13
• 25 Params (Ints, In) - 7.8
• 25 Params (Ints, InOut) - 21.13
• 50 Params (Ints, In) - 12.9
• 50 Params (Ints, InOut) - 39.88
• 2 Params (Varchar, In) - 3.83
• 2 Params (Varchar, InOut) - 4.55
• 25 Params (Varchar, In) - 9.28
• 25 Params (Varchar, InOut) - 20.0
• 50 Params (Varchar, In) - 15.14
• 50 Params (Varchar, InOut) - 34.81

As expected the InOut are more costly. It does appear to have a significant impact on performance the more parameters that you have. Strings seem to be faster with lots of parameters for the InOut, while slower for the In.

[MarkCulp · Accepted solution]

The process that is used by SA to pass parameters in a call to a stored procedure is exactly what you would expect:

• SA maintains a stack of call frames
• Each frame contains a number of pieces of information including the reference to the procedure and its related attributes
• A call to a procedure implicitly creates a new scope and the symbol table for the new scope is initialized to contain the parameters that were passed in the call
• When a procedure returns, any parameters that are declared as "out" are copied back to the caller's source variable.

A few additional things should be noted:

• If the procedure is a simple function - e.g. a single select statement - then the function may be automatically inlined into the caller's statement.
• If you are calling a function which is declared deterministic (and this is the default for functions), then SA will cache the return value so that it does not need to call it a second time when presented with the same inputs.
• Long strings are copied by reference (to an immutable string) so that the cost of copying the string is not incurred until such time that the parameter value was actually changed (and even then SA does a few tricks to keep the cost of working with big strings low).

Therefore it would be natural for the cost of INOUT parameters to be higher than IN parameters since extra work is required to copy the values back to the caller's frame.

Excluding the overhead of doing an invocation of a procedure (allocating a new stack frame, initializing a new scope, etc), the cost of an invocation will be roughly proportional to the number of parameters. The cost of each parameter will vary slightly based on the type of the parameter but this variation will be negligible when looking at the total cost of the procedure call.

[Former Member · Accepted solution]

Great stuff, thanks much Mark. A related question, I've been moving code that is in a procedure 3 or 4 times to a separate stored procedure as I modify someone elses code. What are your suggestions for keeping the performance of this the same?

We're trying using console variables (create variables) for this, do they have overhead associated with them that makes this a bad practice? Besides being global and thus a bad practice already. 🙂

[MarkCulp · Accepted solution]

The cost of 'executing a sequence of statements' and 'calling a procedure that executes the sequence of statements' is obviously not zero since there is the additional cost of doing the call. Whether this additional cost is significant will be dependent on the frequency of usage and the relative cost as compared to the total cost of the complete operation. So you need to weigh the factors and determine what is best for you circumstance.

[MarkCulp · Accepted solution]

Whether or not to use global variables is a choice that you will need to make - there is likely minimal performance differences except for the obvious cost savings for not needing to pass the parameter(s). i.e. each procedure is only parsed and analyzed once so once a variable reference is bound to its (global or parm) value there is no difference on how it is used.

[MarkCulp · Accepted solution]

The only overhead of global variables is that the variable definitions is kept at the connection level and persists for the life of the connection. (continued next comment....)

[MarkCulp · Accepted solution]

If you have a long living connection then you may want to consider setting the global variable value to null after you are finished using it so that its associated resources are mimimum - i.e. if you set a global to a long long string (e.g. during processing of a particular request) and then do not use it again, the variable will continue to use memory resources while the connection remains action. (Note that memory assigned to an inactive connection will eventually be paged to the db's temp file)

[MarkCulp · Accepted solution]

BTW: Looks like this StackExchange thing limits the length of comments and hence the multiple replies above.