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).
FROM Somewhere
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.
It's almost certain, based on the limited amount you've stated so far, that transaction date should be part of that index. Whether customer id should appear first also, I can't tell yet.
Do you (almost) always process one customer at a time, or do you tend to process all customers over a given time period? Those are the types of qs that help determine the best clus index. And, as always, we will also need to look at missing index stats and index usage stats also.