Unable to use user defined table SQL Function in a SQL script in Fabric

[mtomova]

Hi all,

I have a user defined table SQL Function created in Fabric. The function works fine when I use it on it's own and I pass the parameters.

However, when I try to nest it in another SQL query, the script runs endlessly. There is no error message, it just runs...

I know for a fact that the function can be nested in another SQL script, because I have used it multiple times in Azure Synapse Analytics. 

If I use the same script in Azure it runs for 2 secs, but in Fabric it just runs forever like I've said.

I am thinking that there is something I am not doing right, so hopefully someone will be able to help.

I have my function here:

and this is how I call it in the main SQL script:

WITH CTE1_Complaints AS

(

    SELECT cc.id_Contact,

        cc.compId,

        cc.status,

        cc.zu_creationTimeStamp_um,

        CAST(cc.dateResolved AS DATE) AS 'dateResolved',

        CAST(cc.ReceivedDate AS DATE) AS 'ReceivedDate'

    FROM CustomerComplaints cc   

)

SELECT *,

    (

    SELECT COUNT(*)

    FROM dbo.CountWorkingDays(ReceivedDate, dateResolved)

    ) AS 'Test_func'

FROM CTE1_Complaints

I have tried having 'dbo.' before the name of the function, and also tried without it, simply doens't work.

Like I have said I don't get an error message, the script just runs 'forever' 

I hope that this information is enough.

Thanks,

Maria

[Anonymous · Accepted solution]

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

[mtomova]

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

[mtomova]

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