UDF nightmare

Hello everybody ,

i have a severe problem here regarding production DBs

the system heavily utilizes UDFs in queries in bad behaviour

being used in select and where clauses , the queries can take considerable time

ihave managed to enhance issues regarding indexes and select clauses ,but UDf in where clauses still a problem bcz they contain some logic icann't make it inline

here is an example :

select col1,

col2,
col3,
dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) ,
dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice) ,

from listing
inner join ResidentialSeller on listing.id=ResidentialSeller.id
where
(@TotalAreaFrom is null or dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) >= @TotalAreaFrom )
and (@TotalAreaTo is null or dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) <= @TotalAreaTo )

and
(@SalePriceFurnishedFrom=0 or (furnishedSalePrice>0anddbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice)>=@SalePriceFrom))
and
(@SalePriceFurnishedTo=1000000000 or(furnishedSalePrice>0 anddbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice)<=@SalePriceTo))



the two UDFs fn_ConvertUnit is used to convert from measure unit to another (like from Meter to feet)

and fnCurrencyConvert is from currency to another (like from $ to Euro) based on predefined rate

CREATE FUNCTION [dbo].[fn_ConvertUnit]
(@Uvalue float,
@CfromID tinyint,
@CToID tinyint
)
RETURNS float AS


BEGIN

if ((@Uvalue is null) or (@CfromID is null) or (@CToID is null))
begin
return null
end
declare @fac int
declare @temp float
if (@CfromID=@CToID)or (@Uvalue=0)
begin
return @Uvalue
end

if exists(select * from LookupUnitConversion
where [LookupUnitConversion_UnitFrom]=@CfromID and [LookupUnitConversion_UnitTo]=@CToID
)
begin
select @fac=LookupUnitConversion_Factor
from LookupERAUnitConversion
where [LookupUnitConversion_UnitFrom]=@CfromID and [LookupUnitConversion_UnitTo]=@CToID

if (@fac>1)
begin
set @temp=1.79E308 /@fac
end
else
begin
set @temp=1.79E308 *@fac
-- set @temp=@fac
end

if (@temp<@Uvalue)
begin
-- return 1.79E10
return 1000000000-- A Big number enough to be compared by any number user can type

end
else
begin
RETURN @Uvalue*@fac
end
end

if exists(select * from LookupUnitConversion
where [LookupUnitConversion_UnitFrom]=@CToID and [LookupUnitConversion_UnitTo]=@CfromID
)
begin
select @fac=LookupUnitConversion_Factor
from LookupERAUnitConversion
where [LookupUnitConversion_UnitFrom]=@CToID and [LookupUnitConversion_UnitTo]=@CfromID
if (@fac>1)
begin
set @temp=1.79E308 /@fac
end
else
begin
set @temp=1.79E308 *@fac
end

if (@temp<@Uvalue)
begin
return 0
end
else
begin
RETURN 1/(@Uvalue*@fac)
end
end

return -1
END

-------------------------------------------------
-------------------------------------------------

CREATE FUNCTION fnCurrencyConvert
(@FromCurrency int,
@ToCurrency int,
@Country int, -- This prameter is useless
@AmountOfMoney float
)
RETURNS float
AS

BEGIN


if( (@FromCurrency is null ) or (@ToCurrency is null) or (@AmountOfMoney is null))
begin
return null
end
declare @temp float -- will carry the max value without exception

if @FromCurrency=@ToCurrency or @AmountOfMoney=0
begin
return @AmountOfMoney
end
declare @fac float
if exists(select * from CurrencyConvertion
where cFromID=@FromCurrency
and cToID=@ToCurrency
)
begin
select @fac=Factor
from CurrencyConvertion
where cFromID=@FromCurrency
and cToID=@ToCurrency
order by ConvertionDate desc
if (@fac>1)
begin
set @temp=1.79E308 /@fac
end
else
begin
set @temp=1.79E308 *@fac
end
if (@temp<@AmountOfMoney)
begin
return 1.79E308
end
else
begin
RETURN @AmountOfMoney*@fac
end
end

if exists(select * from CurrencyConvertion
where cFromID=@ToCurrency
and cToID=@FromCurrency
)
begin
select @fac=Factor
from CurrencyConvertion
where cFromID=@ToCurrency
and cToID=@FromCurrency
order by ConvertionDate desc
if (@fac>1)
begin
set @temp=1.79E308 /@fac
end
else
begin
set @temp=1.79E308 *@fac
end
if (@temp<@AmountOfMoney)
begin
return 0
end
else
begin
RETURN 1/(@AmountOfMoney*@fac)
end
end

return -1
END


i will appreciate any help regarding these two Udfs as they r the most problematic ones , other suggesions r highly appreciated

thanks for ur time

regards

what kind of indexes you have on LookupERAUnitConversion and CurrencyConvertion
I know this kinda problem stinks. in-line UDFs are famous for bad performance!
ahhh.. flexibility!

also what size are these tables.

sorry.. just thinking.. if these tables are small you could shove them into a table variable or use a CTE (check limitations) to pull the data once rather than making trips to the table for each select.

Thanks Mike for your contribution
1- LookupEraUnitConversion: PK clustred index on LookupERAUnit_ID , combined Nonclustered index on LookupUnitConversion_UnitFrom , LookupUnitConversion_Unitto .
the same applies for currency table , with column name changes .
these tables are quite small , not more than 200 rows.
CTE isn't applicable as this is SQL server 2000 .
do u mean by putting it in memory is to create a table variable at the beginning of the SP and retrieve data from ? well , this can be achieved , but the main
problem is in the main tables whose thier rows are processed one by on , the table are quite big (thousands of rows)
Regards

I think i can't pass table variable to Udf

Ok. then I am unsure. I will have to give it more thought

Like Mike suggested in his first response - look up the parameters, and copy them into a table variable, before the main query is executed.
With the parameter lookups already done, you no longer need a UDF - just use CASE syntax to resolve the complexity.

How come ???!!! , there r like more than 100 currencies with changing factors each day, and each row of the base tables may have different currency that needs to be converted to the querying currencies. in that manner , there will be like thousands of Case conditions.

Huh? The UDF uses only a handful of IF constructs - you should be able to translate those to a single CASE construct.
Think set-based. You may have to do a series of INSERTs on the temp table to cover all variations on the lookup logic.