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