Are there techniques to replace
- UDF
- tabled value functions
with something else?
or techniques to make them acceptable? (fast, multithreading and with good estimations)
Sql-server – replace user defined functions (UDF) and tabled value functions(TVF)
functionsset-returning-functionssql-server-2017
Related Solutions
SSMS (via SMO) allows you to script it at the category (function) level. It won't allow you to specify only table valued functions.
You could also look at something like this article for using PS to automate the creation. You might be able to filter the objects being scripted by the function type (tvf, scalar, etc)
There comes a time when you have to decide what is more important to you, maintenance of the code or the speed at which it runs? The reason it is running slow is because UDF's get processed on a row-by-row basis - SQL Server performs best using set-based operations. There is no reason why you cant keep your UDF's as there may be time where it is more practical to use them than to expand out the query.
My advice is this: If your data set is small feel free to use the UDF's; but if you are working with a large data set then take the time to write, test and optimize the query to obtain the best results - it will benefit you in the long run when you don't have users complaining their system is slow.
Related Question
- PostgreSQL – When to Use Stored Procedure vs User-Defined Function
- SQL Server UDF or Stored Procedure – Limiting External Access
- SQL Server – Using PWDENCRYPT in User-Defined Functions
- TSQL – Using Intermediate Results in User Defined Functions
- SQL Server – Efficiency of Scalar UDF vs TVF
- PostgreSQL – User Defined Functions (UDF) Overhead and Performance
- Mariadb – Collation conflicts between views and functions when using utf8mb4_unicode_ci collation
Best Answer
No easy way unless you are on SQL2017 for table valued functions and not UDFs.
However on SQL 2019 scalar UDFs May perform little better but there are still lots of catches involved
By default parallelism is inhibited while using the UDFs within code, mind you the function part itself can go parallel though.
Your best part if not on latest sql server version would be to inline the function. That is simply copying the work of function inside your code rather than calling function from the code.