I am currently in the process of generating technical analysis result sets for my stock market historical data, in SQL Server 2008. In doing so I am realizing more and more how terrible relational databases / SQL are at this kind of thing.
One possible solution is to use CLR functions and do these things in good old procedural algorithms using C#. However, CLR functions don't support (yet?) being passed table-valued parameters, and as a result I would have to marshal my input time series into an XML or CSV parameter. This is fine when you are operating on a small set of values -- however I have moral objections to doing this when you have hundreds of thousands, or even millions of rows, to operate on.
You can also have the CLR function fetch the data set itself, but extensive data retrieval in CLR functions is generally a No No from the standpoint of performance.
Additional NOTE: this can't be done in CLR custom aggregates, because aggregates don't support ordered sets.
Here I will outline the various strategies I have tried, and conclude with which approach I think is the best for this kind of problem.
No comments:
Post a Comment