Should I be using "Custom Functions" in SQL Server or C #?
I have a rather complex math function that I was told that needs to be implemented as a UDF in SQL Server in order to be efficiently used from a SQL query.
The problem is that it has to be very efficient as it can run thousands of times per second and afterwards I heard that UDFs are very inefficient.
Someone suggested that I could implement a function in C # instead, and that it would be much more efficient.
What should I do?
a source to share
Complex math functions will run much faster in C # than T-SQL. It's worth trying.
Edit . In response to your comment, I found this blog post which says:
User-defined scalar functions are probably the most obvious candidates for using SQLCLR. There are two main reasons for this. First, CLR functions actually have lower call overhead than T-SQL functions. T-SQL functions require the runtime to create a new T-SQL frame, which is expensive. CLR functions are embedded in the plan as a function pointer for direct execution.
And finally, one test:
With this prime check function, the performance advantage of SQLCLR over T-SQL is an order of magnitude higher!
So C # sounds like the way to go.
a source to share
Standard UDFs in SQL Server can be ineffective. Because they are compiled each time they run (because they are not included in the query plan for the entire SQL statement executed by the Query Processor).
This is because the standard UDF defines a processing algorithm that cannot be "folded" into a general query plan executed by an external sql ...
The user -scored inline table function , otoh, because it is simply defined as the sql statement itself, can be folded into an overall query plan and is therefore extremely fast and efficient.
For a complex math function, this is generally probably not possible, or at least very difficult), but if your function can be written as an inline UDF, this is definitely the way to go. Otherwise, you are probably better off doing it in code.
a source to share
As @otavio says you need to get measurements before making a decision. However, you should also consider a function that itself, in a larger set or optimization. For example, you perform this function many times on the same data, or you can do it as needed. Can you store the results, for example, and can you write a larger function that works on a dataset than a time call can, etc.
a source to share