calling a function in select query
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))
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.