I have a fairly complicated mathematical function that I've been advised should be implemented as a User Defined Function in SQL Server so that it can be used efficiently from within a SQL query.
The problem is that it must be very efficient as it may be executed thousands of times per second, and I subsequently heard that UDFs are very inefficient.
Someone suggested that I could implement the function in C# instead, and that this would be much more efficient.
What should I do?
I would do both, measure the times and stick with the one with better performance.
Complex mathematical functions will execute much more quickly in C# than T-SQL. It's well worth trying.
Edit In answer to your comment, I found this blog post, which states:
User-defined scalar functions are likely the most obvious candidates for SQLCLR usage. There are two primary reasons for this. The first is that CLR functions actually have lower invocation 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 in conclusion of a single test:
In the case of this prime number validating function the performance advantage of SQLCLR over T-SQL is an order of magnitude in speedup!
So, C# sounds like the way to go.
Standard UDFs in SQL Server can be inefficient Because they are compiled each time they run, (as they are not included in the query plan for the entire SQL statement executed by the Query Processor).
This is because a standard UDF defines a processing algorithm that cannot be "folded" into the overall query plan that the outer sql statement is executing...
An inline table valued user defined function, otoh, because it is simply defined as a sql statement itself, can be folded into the overall query plan, and therefore is extremely fast and performant.
For a complex math function, generally this is probably not possible, or at minimum will be very difficult), but if your function can be written as an inline UDF, that would definitely be the way to go. Otherwise, you're probably better off doing it in code.
As @otavio says you need to obtain measurements before deciding. However should also consider the function it self in a larger set or optimisations . For example are you running this function many times on the same data, or could you perform this on as needed basis. Can you store the results for example, and are you able to write a bigger function that operates over a set of data than calling it may times, etc.