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.
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.
The process that is used by SA to pass parameters in a call to a stored procedure is exactly what you would expect:
A few additional things should be noted:
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.
answered 16 Nov '09, 17:18