I have a SQL Server 2014 database which has a large transaction file (millions of records). Currently a process is running in C# that has to evaluate balances for customers and scans the transaction file for transactions between ranges of dates. The ranges required for each balance evaluation are set via parameters that are held in the database and vary for each customer.
The existing C# process is looping through each customer record and calls back the parameters from SQL via a SQL Stored Procedure and calculates the dates required (and other things) and then uses another Stored Procedure to fetch the result of the transactions from SQL (ie sum or average of the records). The only thing being transmitted for each customer is the parameter information and then the resulting value from the transaction table.
The existing process is performing ok but I really need to achieve a faster result. Would I be better off implementing the logic of evaluating the balance in SQL CLR or T-SQL Functions / Stored Procedures.
If using CLR then I will have to use a SQLDataReader (or similar with Context Connection) to get the various values required to calculate the dates based on the returned values and then call a stored procedure to get the required value from the transaction table. I could avoid using a stored procedure call from CLR by executing the statement if this has a performance gain.
Ideally I would like to implement a function (CLR or T-SQL) so I can use something like
INSERT INTO ResultsTable (CustomerID, Balance)
SELECT CustomerID, GetBalance(parm1ID, parm2ID, parm3Date, etc).
I am assuming that SQL Server will be much faster at crunching the numbers than a C# program that is fetching loads of bits of the puzzle from SQL and then having to write the data back to a table in SQL afterwards.
Would really appreciate some constructive advice on the best direction to proceed down to get the best performance.