SQL Server Performance

UDF nightmare

Discussion in 'T-SQL Performance Tuning for Developers' started by hello_everybody, Oct 17, 2007.

  1. hello_everybody New Member

    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

  2. MichaelB Member

    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!
  3. MichaelB Member

    also what size are these tables.
  4. MichaelB Member

    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.
  5. hello_everybody New Member

    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
  6. hello_everybody New Member

    I think i can't pass table variable to Udf
  7. MichaelB Member

    Ok. then I am unsure. I will have to give it more thought:)
  8. Adriaan New Member

    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.
  9. hello_everybody New Member

    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.
  10. Adriaan New Member

    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.

Share This Page