Which is the best approach SQL CLR, SQL Functions, SQL Stored Procedures?
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.
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.
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
The key to performance is getting the best clustered index on every table. Hint: This is most often not an identity column.
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.
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.
Indexes are not my question here but thanks for your input I take your point. I have the relevant indexes in place for the retrieval of the data from the transaction file and that side of things is working great. I have been performance monitoring the SQL server and checking for missing indexes and have added stats as well and the retrieval speed from the transaction table is not an issue its really quite quick.
I guess my question is more along the lines of: Are there performance advantages to using T-SQL natively for this type of processing over a coded solution running outside of SQL and using ADO.NET to access the SQL database? I guess SQL can at least optimise queries in T-SQL where it can only deal with what is being requested from the program.
If others have found that running bulk balance style evaluations over large datasets is better to be done in SQL directly then I would invest the time to replicate the process in T-SQL to see what results I get compared to the external coded approach.
I think I will just have to bite the bullet and do it and see what results I get.
I guess my question is more along the lines of: Are there performance advantages to using T-SQL natively for this type of processing over a coded solution running outside of SQL and using ADO.NET to access the SQL database? I guess SQL can at least optimise queries in T-SQL where it can only deal with what is being requested from the program.
If others have found that running bulk balance style evaluations over large datasets is better to be done in SQL directly then I would invest the time to replicate the process in T-SQL to see what results I get compared to the external coded approach.
I think I will just have to bite the bullet and do it and see what results I get.
I don't believe there are, as long the .NET code is reasonable. Calling a stored proc might have some minor performance edge, because it's more likely to cache a plan, but that's a minor performance boost unless the code is really huge.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server
From novice to tech pro — start learning today.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
As for your question on the best approach, the answer is: it depends! As you can read in my article:
I still believe that if you are dealing with set-based operation, pure SQL will win over CLR.