UDF in Select | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

UDF in Select

Hi, I’ve got a select like this : Select
Column0,
Column1= Column4 * udf_CalcValue(Column0),
Column2= Column5 * udf_CalcValue(Column0),
Column3= Column6 * udf_CalcValue(Column0),
Column4,
Column5,
Column6,
Column7 from Table1 the udf returns smallmoney.
is there a way to only execute the UDF once per row? this way it calculates the same value 3 times, which includes a number of DB reads each time. the select is returning about 3K rows, even like this it’s much faster than a cursor.

try the following Select
Column0,
Column1= Column4 * U,
Column2= Column5 * U,
Column3= Column6 * U,
Column4,
Column5,
Column6,
Column7 from ( Select
Column0,
udf_CalcValue(Column0) AS U,
Column4,
Column5,
Column6,
Column7
from Table1 ) t

Thanks, After tinkering with the idea and reviewing execution plans, atleast with my query, it turns out the original way has a lower cost and runs faster. in my query the sub select causes 1 hash join to be replaced with 2 sorts and a merge join. while index changes or hints could resolve that I’d have the same plan at best. looking at the execution plans the cost for the scalar functions is the same in both. either the number of rows read in the UDF are not making an implact or maybe the optimizer already figured it out and is not re-executing for the same row. whatever the reason,thanks anyway, I’ll keep that approch in mind for other projects just in case.
]]>