I'm working on a system with a whole heap of SPs, many of which do a lot of complicated calculations. Some of these need to do the following sort of thing:
- SP1 defines a set of data upon which calculations need to be based.
- It passes that set to SP2, which does the calculation.
My question relates to the seemingly-innocuous "passes that set to SP2". We're currently achieving this by:
- SP1 fills up a table X with the dataset and calls SP2.
- SP2 does its calculation based on the data in X.
- When SP2 finishes, SP1 clears its data out of X.
This seems to work but we do get blocking / deadlocking when accessing X. And it means we have (yet) another table in our database which we're using to store transient data.
We have tried:
- SP1 creates a temporary table Y, fills it up with the dataset and calls SP2.
- SP2 does its calculation based on the data in Y.
- When SP2 finishes, SP1 drops table Y.
This usually turns out to be slower than the "permanent" table approach. (As an aside, it also means that you don't get intellisense when editing SP2, because the definition of temporary table Y is not in context.)
More recently, we've tried user-defined table types:
- SP1 declares a local variable, Z, of a certain UDTT, fills it up with the dataset and calls SP2, passing Z as a (read-only) parameter.
- SP2 does its calculation based on the data in parameter Z.
This is great because it makes it much clearer what data SP2 is working on - it's the data in the parameter, not data in some distant table. However, this has led to worse performance, particularly when there are many rows in Z - presumably because of SQL assuming it contains only 1 row (or it may be 100), irrespective of how many rows it actually contains.
We tried adding "Option (Recompile)" to the statements using parameter Z, but this had mixed results in terms of performance.
We haven't tried the approach in which SP2 caches the contents of parameter Z in its own temporary table and then uses this, the benefit being that SQL will maintain accurate statistics on it.
So my question is; do any of you clever people out there have any suggestions for the optimal approach in terms of performance?