This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 91%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERK%3C/text%3E%3C/svg%3E)
We are working on a UDF solution for MSSQL Server. Currently we have scalar TSQL UDFs which calls CLR functions. CLR functions (written in C#) further calls C functions present in a DLL file which is imported by C# layer using DLLImport. Also due to some requirement, our CLR function needs database access therefore we have added following properties to them [SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
With this design we observe very less performance for the UDFs.
We further investigated and found that when we submit a query involving our UDF, like following
*insert into test_destination(id,name) select id, master.dbo.my_udf1(name) from test_source; (*test_source have 1 million records)
then our UDF is called sequentially one by one for each record i.e., 1 million times. We believe SQL server is not enabling parallelism here for processing the query. Due to this performance is bad.
To enable parallelism for our scalar UDFs, we tried
but SQL server is not enabling parallelism with these as well.
We did not tried QUERYTRACEON query hint as it is not recommended for production environments.
So is there any way by which we can enable parallelism in SQL server for scalar UDFs which calls a CLR function?
Is it possible to remove the database access and to add arguments to my_udf1?
then our UDFs is called sequentially one by one for each record i.e., 1 million times. We believe SQL server is not enabling parallelism here for processing the query. Dee to this performance is bad.
Of course and it doesn't matter if you use a CLR or a plain T-SQL UDF; it's always called per each record; what else do you expect? And that's why it is not a good idea to use a UDF on large data.
Yes, UDF will be called for each record but it is called sequentially using a single thread. Due to this overall execution time is large and performance is bad. What we want is to enable parallelism for scalar UDFs, so that large data set can be processed efficiently and fast.
Parallelism or not parallelism mainly depends on your base query, not on the UDF. See
Yes, it all depends on database query engine decision. But we want to force it to use parallelism. In other database this is done by default or by using some hints. Like on oracle we can use parallel hint and Teradata by default execute the query using multi-threading.
But with only SQL server we are facing this performance issue. So we are exploring ways to force/enable parallelism for our UDFs.
I don't know anything abaout your UDF, because you didn't provided an informations abaout.
May it's possible to re-design the UDF to a TVF = "Table Valued Function", which may perform better.
See
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 61%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
SQLServer has many restrictions on insert parallelism.
a million row insert should not be too bad, but I suspect that your UDF is creating nested query loop. if the UDF was removed and replaced with sql joins, the optimizer could use a different join option.
If I understand it correctly, you have a T-SQL function, which calls a CLR function which in its turn can make calls back to the database. I'm wondering if this can be safe to parallelise. (You may know what those callbacks do, but I and SQL Server does not.)
Insert the data without the UDF. Then set up a Service Broker service with multiple queue readers, and send messages for each partition you divide the data into, so that you have control over the parallelism of the update.