Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hungrytom
Starting Member
6 Posts |
 Posted - 2010-07-01 : 06:21:16
|
| Hi,I would like to create a calculation in SQL Server that takes a table of input rows and calculates a load of output values using a sequence of functions to encapsulate each element (or block) of the calculation sequence.Put another way... 1) I want to take a table of input where each row represents a set of inputs for a single complete calculation.2) I want to pass each table row to my first (user-defined) function which will calculate a bunch of things and return them as a table row.3) I will then take that table row (output from 2) and use it as input to the next function, which will calculate some things and return them as a table row.4) I will then take that table row (output from 3) and use it in the next function... etc.etc.etc. 5) Until eventually 12 functions have been applied to the input data table and the intermediate results of the preceding function.The end result should be a table containing the complete input and output of all the functions together. In this way I will use a chain of functions to calculate an output row for each row of input.The challenge is that there are millions of input rows and I need to find a way to speed this up. Therefore, I have looked at CLR Table-Valued Functions as these offer an opportunity to encapsulate the calulcation logic in neat, C# functions that are more appropriate for this procedural code. At the same time, I figured I could use the streaming-results element of the table-valued functions to pass the outputs of one calculation block straight to the next as input. This way I hoped I could create a chain of calculations where the input data and intermediate results flowed through the calculations and overall results were streamed out of the complete sequence as they were available.I figured this streaming-chain approach would allow us to get the most out of our hardware which has 2 Xeon X5680 processors and 96GB of RAM to play with. I would like to ensure that I max out all those processors and use all the RAM available for this calculation to ensure maximum speed. I have assumed that by using streaming TVFs and chaining them together, SQL Server will parallellise as much of the calculation as possible and spread the work over all the cores..The first problem is that although I know a (little) bit about C#, I am new to writing CLR User Defined Functions for SQL Server and I am struggling create a function that will take a table valued parameter.I am also not sure if this will work and I could use any suggestions or advice anyone has about how I can do this differently/better. Any configurations I might need to switch on/off in SQL Server to encourage parallelisation, etc..I look forward to hearing what people think and if this is in fact complete jackassory!Thanks in advance,Tom |
|
|
|
|
|
CLR UDFs, Streaming TVFs and Table-valued Params