Forum Discussion
Unable to use user defined table SQL Function in a SQL script in Fabric
Hi mtomova,
Thank you for reaching out to Microsoft Fabric Community Forum.
The issue happens because the user-defined table-valued function (CountWorkingDays) is placed inside a subquery in the SELECT clause. In Microsoft Fabric, this means the function runs separately for every single row in the dataset, which can slow things down massively and even make the query run indefinitely.
Azure Synapse is designed to handle these kinds of function calls more efficiently, optimizing them in the execution plan. Fabric, on the other hand, doesn’t optimize them in the same way, which leads to performance issues.
Instead of using the function inside a subquery, a better approach is to use CROSS APPLY. This helps the function return multiple rows for each input row in a much more optimized way. It processes data in sets rather than row by row, making the query run faster and more efficiently.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
Vinay Pabbu
Hi mtomova,
Thank you for reaching out to Microsoft Fabric Community Forum.
The issue happens because the user-defined table-valued function (CountWorkingDays) is placed inside a subquery in the SELECT clause. In Microsoft Fabric, this means the function runs separately for every single row in the dataset, which can slow things down massively and even make the query run indefinitely.
Azure Synapse is designed to handle these kinds of function calls more efficiently, optimizing them in the execution plan. Fabric, on the other hand, doesn’t optimize them in the same way, which leads to performance issues.
Instead of using the function inside a subquery, a better approach is to use CROSS APPLY. This helps the function return multiple rows for each input row in a much more optimized way. It processes data in sets rather than row by row, making the query run faster and more efficiently.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
Vinay Pabbu
Hi, Anonymous
Thank you so much for getting back to me.
Having this explanation really helps me understand how Fabric works.
I will not have a chance to test the CROSS APPLY option you are suggesting, but once I do, I will make sure to write back here and 'accept as solution' if it resolves my problem.
Many thanks,
Maria
Hi Anonymous ,
I have tried what you have suggested, re-shaping the code and using CROSS APPLY, however the query again is running indefinitely.
I am not quite sure how is it possible to use user defined SQL functions in Fabric.
In the documentation it says that one can create UDF in Fabric:
CREATE FUNCTION (Azure Synapse Analytics and Microsoft Fabric) - SQL Server | Microsoft Learn
I know that scalar functions are NOT supported atm, but my function returns a table, so that's not the problem...
In some instances one does need a function to perform what they are after and I am really curious to understand how can we actually use function in our SQL scripts in Fabric.
For example, the query which runs indefinitely in Fabric is executed in Synapse for 1 second.
Thanks,
Maria
