calling a function in select query

PARAG 2016-06-01 17:57:38

Hi

I am populating a temp table from a select query and in the select query I am calling a scalar valued function, passing the table columns as parameters to the function

Is there any performance hit involved in calling a scalar UDF as part of the select statement ?

Here is my select statement

SELECT DISTINCT
    jps.JJID
    , jps.PrSKU
    , csn_warehouse.dbo.fnOrderCSV(CASE WHEN piid1 is not null THEN CAST(piid1 as varchar(max)) ELSE '' END 
                                    + CASE WHEN piid2 is not null THEN ',' + CAST(piid2 as varchar(max)) ELSE '' END 
                                    + CASE WHEN piid3 is not null THEN ',' + CAST(piid3 as varchar(max)) ELSE '' END ) AS 'OptionList' INTO #tmpActiveJJID
    FROM csn_product..tbljoinProductSupplier jps WITH(NOLOCK)
    INNER JOIN csn_order..tblSupplier s WITH(NOLOCK) ON COALESCE(s.SuParentSuID, s.SuID) = jps.SuID
    INNER JOIN csn_product..tblProduct pr WITH(NOLOCK) ON jps.PrSKU = pr.PrSKU
    INNER JOIN csn_order..tblSupplierExt se WITH(NOLOCK) ON s.SuID = se.SuID 
    WHERE PrStatus IN (2, 3, 4, 13, 17) 
    AND Dropships = 1
    AND SuSiID = 1
    AND ((ISNULL(SuInventoryFeedLevel,0) <> 2 AND SuParentSuID IS NULL) OR (ISNULL(SuInventoryFeedLevel,0) = 2 AND SuParentSuID IS NOT NULL))

Thanks,
Parag

SQLkiwi 2016-06-02 05:56:29
Yes, there is a huge overhead.

In your example, the function would be executed over 5 million times. Even if the function performs no data access, the overhead of simply calling the function that many times will be very noticeable. If the function accesses data (e.g. other tables) it will be even worse.

Using a T-SQL scalar function also means the query plan cannot use parallelism.

In general, T-SQL scalar functions are best avoided (unless, perhaps, you can guarantee they will only be called a very small number of times). There are almost always better alternatives.