SQL Server Performance Forum – Threads Archive

Function calls and Variables…

10 function calls (returning a int value) in an update statement and a value substituted inplace of functions, takes equal time for execution. for example.. the code below executed in 57 secs no matter i use functions or constants. I wonder how? UPDATE dbo.TBL_WH_PENDING
SET COUNTRY_SELL_PRI = dbo.UDF_GET_COUNTRY_SELLING_PRICE (T1.UPC_ID,T1.TRAN_DATE_ID,T2.STORE_CTH_WGT_FLAG, T1.STORE_ID)
,COUNTRY_SELL_PRI_EXR = (CASE WHEN T2.COUNTRY_SELL_PRI_CUR_ID = dbo.UDF_CURRENCYX_CODE2ID(‘GBP’) THEN 1.0
ELSE dbo.UDF_EXCHANGE_RATE(T2.COUNTRY_SELL_PRI_CUR_ID, dbo.UDF_CURRENCYX_CODE2ID(‘GBP’),T1.TRAN_DATE_ID)
END)
,COUNTRY_SELL_PRI_OD_EXR = (CASE WHEN T2.COUNTRY_SELL_PRI_OD_CUR_ID = dbo.UDF_CURRENCYX_CODE2ID(‘GBP’) THEN 1.0
ELSE dbo.UDF_EXCHANGE_RATE(T2.COUNTRY_SELL_PRI_OD_CUR_ID, dbo.UDF_CURRENCYX_CODE2ID(‘GBP’),T1.TRAN_DATE_ID)
END)
,STORE_SELL_PRI_EXR = (CASE WHEN T2.STORE_SELL_PRI_CUR_ID = dbo.UDF_CURRENCYX_CODE2ID(‘GBP’) THEN 1.0
ELSE dbo.UDF_EXCHANGE_RATE(T2.STORE_SELL_PRI_CUR_ID, dbo.UDF_CURRENCYX_CODE2ID(‘GBP’),T1.TRAN_DATE_ID)
END)
,TRF_PRI_EXR = (CASE WHEN T2.TRF_PRI_CUR_ID = dbo.UDF_CURRENCYX_CODE2ID(‘GBP’) THEN 1.0
ELSE dbo.UDF_EXCHANGE_RATE(T2.TRF_PRI_CUR_ID, dbo.UDF_CURRENCYX_CODE2ID(‘GBP’),T1.TRAN_DATE_ID)
END)
,TRAY_COST_PRI = (CASE WHEN T2.TRAY_COST_PRI IS NULL THEN dbo.UDF_TRAY_COST_PRI( T1.UPC_ID, T1.TRAN_DATE_ID ,T1.DEPOT_ID, T1.STORE_ID, T2.TRF_CTH_WGT_FLAG,T1.DELV_TYPE_ID, T1.SUPP_ID, T2.UNITS_PER_TRAY)
ELSE T2.TRAY_COST_PRI
END)
,PRD_VAT_PER = dbo.UDF_RD_VAT_PER (T1.UPC_ID,T1.STORE_ID)
FROM dbo.TBL_WH_WRK_ALLOCATIONS T1
INNER JOIN dbo.TBL_WH_PENDING T2
ON T1.TBL_WH_ALLOCATIONS = T2.TBL_WH_ALLOCATIONS UPDATE dbo.TBL_WH_PENDING
SET COUNTRY_SELL_PRI = dbo.UDF_GET_COUNTRY_SELLING_PRICE (T1.UPC_ID,T1.TRAN_DATE_ID,T2.STORE_CTH_WGT_FLAG, T1.STORE_ID)
,COUNTRY_SELL_PRI_EXR = (CASE WHEN T2.COUNTRY_SELL_PRI_CUR_ID = 2 THEN 1.0
ELSE dbo.UDF_EXCHANGE_RATE(T2.COUNTRY_SELL_PRI_CUR_ID, 2,T1.TRAN_DATE_ID)
END)
,COUNTRY_SELL_PRI_OD_EXR = (CASE WHEN T2.COUNTRY_SELL_PRI_OD_CUR_ID = 2 THEN 1.0
ELSE dbo.UDF_EXCHANGE_RATE(T2.COUNTRY_SELL_PRI_OD_CUR_ID, 2,T1.TRAN_DATE_ID)
END)
,STORE_SELL_PRI_EXR = (CASE WHEN T2.STORE_SELL_PRI_CUR_ID = 2 THEN 1.0
ELSE dbo.UDF_EXCHANGE_RATE(T2.STORE_SELL_PRI_CUR_ID, 2,T1.TRAN_DATE_ID)
END)
,TRF_PRI_EXR = (CASE WHEN T2.TRF_PRI_CUR_ID = 2 THEN 1.0
ELSE dbo.UDF_EXCHANGE_RATE(T2.TRF_PRI_CUR_ID, 2,T1.TRAN_DATE_ID)
END)
,TRAY_COST_PRI = (CASE WHEN T2.TRAY_COST_PRI IS NULL THEN dbo.UDF_TRAY_COST_PRI( T1.UPC_ID, T1.TRAN_DATE_ID ,T1.DEPOT_ID, T1.STORE_ID, T2.TRF_CTH_WGT_FLAG,T1.DELV_TYPE_ID, T1.SUPP_ID, T2.UNITS_PER_TRAY)
ELSE T2.TRAY_COST_PRI
END)
,PRD_VAT_PER = dbo.UDF_RD_VAT_PER (T1.UPC_ID,T1.STORE_ID)
FROM dbo.TBL_WH_WRK_ALLOCATIONS T1
INNER JOIN dbo.TBL_WH_PENDING T2
ON T1.TBL_WH_ALLOCATIONS = T2.TBL_WH_ALLOCATIONS Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

---

Functions are good and bad…
Good part is you can use them in select statements …
Bad part is… it does row by row operation no matter what you do … Try getting the result set into a temp table or table variable and apply your functions against the result set so that it will finish faster… Mohammed U.

---

]]>