SQL Server Performance

rewrite scalar function to inline table-valued UDFs

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by woodsy1978, Jun 9, 2011.

  1. woodsy1978 New Member

    Hi,

    I'm a DBA trying to rewrite scalar functions that are called by another scalar function that call a view ! i'm working with a team of 8 java developers and they simply do not believe me that changing these scalar functions will take the queires from minutes to a few seconds...

    but my coding skills are not at a developer level, if someone good change the below function to inline table-valued UDF then I think I could do the other ones

    Can anyone help as this would basically secure me a new contract!


    ALTER FUNCTION [dbo].[getCurrencyExchangeRateByDate](@casino_id char(16),@currency_code char(3), @end_date datetime)
    RETURNS float AS
    BEGIN

    declare @retval float;
    declare @casino_curr_code char(3);

    set @casino_curr_code =
    (SELECT TOP 1 currency_code
    FROM Casino
    WHERE
    casino_id=@casino_id
    );

    if (@currency_code = @casino_curr_code) return 1;

    set @retval =

    COALESCE(
    (
    SELECT TOP 1 exchange_rate
    FROM CurrencyExchangeRateHistory
    WHERE
    casino_id=@casino_id and
    currency_code=@currency_code AND
    transact_time <= @end_date
    ORDER BY
    transact_time DESC
    ),0.0);

    return @retval

    any help very much appreicated END[IMG]
    woodsy1978, Jun 9, 2011
    #1

  2. FrankKalis Moderator

    Not entirely sure how this is called, but you might need to LEFT JOIN onto this function to consider the fact that there might not always be a row that meet the filter conditions and then handle any NULLs in the calling statement, but something along these lines should do.

    Code:
    ALTER FUNCTION dbo.getCurrencyExchangeRateByDate (
    @casino_id char(16),
    @currency_code char(3),
    @end_date datetime
)
RETURNS TABLE
AS
RETURN (
    SELECT TOP 1
        CASE
            WHEN @currency_code = C.currency_code
            THEN 1.
            ELSE exchange_rate
        END AS ExchangeRate
    FROM
        CurrencyExchangeRateHistory CER
        JOIN
        Casino C ON CER.casino_id = C.casino_id
    WHERE
        C.casino_id = @casino_id AND
        CER.currency_code = @currency_code AND
        CER.transact_time <= @end_date
    ORDER BY
        CER.transact_time DESC
);
    FrankKalis, Jun 9, 2011
    #2

  3. woodsy1978 New Member

    here is how it is called -

    select TOP 20
    cu.user_id, cu.email_address, cu.first_name, cu.last_name, sum(rpgd.bet_count) 'bet_count',
    sum(rpgd.game_count) 'game_count',
    sum(rpgd.amount * dbo.getInvertCurrencyExchangeRateByDate('ukgqfpjvuno4rph9','GBP', rpgd.status_date)) 'staked',
    sum(rpgd.payoff * dbo.getInvertCurrencyExchangeRateByDate(' ukgqfpjvuno4rph9','GBP', rpgd.status_date)) 'paid',
    sum(rpgd.amount_user_currency) 'staked_in_user_currency',
    sum(rpgd.payoff_user_currency) 'paid_in_user_currency',
    cu.default_currency_code 'currency_code',
    dbo.getCurrencyExchangeRateByDate('ukgqfpjvuno4rph9','GBP', getdate()) 'exchange_rate',
    dbo.getCurrencySymbol(cu.default_currency_code) 'currency_symbol',
    sum(rpgd.payoff_user_currency) - sum(rpgd.amount_user_currency) 'winnings_in_user_currency',
    (sum(rpgd.payoff * dbo.getInvertCurrencyExchangeRateByDate('ukgqfpjvuno4rph9','GBP', rpgd.status_date))
    - sum(rpgd.amount * dbo.getInvertCurrencyExchangeRateByDate('ukgqfpjvuno4rph9','GBP', rpgd.status_date)) ) 'winnings',
    case when sum(rpgd.amount_user_currency) = 0 then 0 else (sum(rpgd.payoff_user_currency) / sum(rpgd.amount_user_currency)) end 'win_percent',
    case when sum(rpgd.bet_count) = 0 then 0 else (sum(rpgd.amount_user_currency) / sum(rpgd.bet_count) ) end 'bet_avg_in_user_currency',
    case when sum(rpgd.bet_count) = 0 then 0 else (sum(rpgd.amount * dbo.getInvertCurrencyExchangeRateByDate('ukgqfpjvuno4rph9'
    ,'GBP', rpgd.status_date)) / sum(rpgd.bet_count) ) end 'bet_avg'
    FROM dbo.ReportPlayerGamblingDaily as rpgd (FORCESEEK)
    INNER JOIN dbo.casinouser cu ON rpgd.user_id = cu.user_id
    WHERE
    rpgd.status_date BETWEEN '2011-05-01' AND '2011-06-01' and
    cu.casino_id = 'ukgqfpjvuno4rph9'
    group by cu.user_id, cu.email_address, cu.first_name, cu.last_name,cu.default_currency_code
    ORDER BY winnings

    how do I rewrite this statement to call the table valued function ?

    the more I investigate, the more this code starts to look like a sql horror movie, but if I could sort this one function then the sql nexus seems to suggest that this may clear up 50% of the problems in the database performance. The horror story is because this function is all over the database and used with the getInvertCurrencyExchangeRateByDate function seen in this query. The 2nd function makes a call back to the first function !

    ALTER FUNCTION [dbo].[getInvertCurrencyExchangeRateByDate](@casino_id char(16),@currency_code char(3), @end_date datetime)
    RETURNS float AS
    BEGIN

    declare @retval float;

    set @retval =
    dbo.getCurrencyExchangeRateByDate(@casino_id,@currency_code,@end_date);

    if (@retval != 0) return 1/@retval;

    return 0;

    END

    Once again many thanks
    woodsy1978, Jun 10, 2011
    #3

  4. SQL Server Helper New Member

    >> how do I rewrite this statement to call the table valued function ? <<

    To rewrite this statement to call the table valued function, simply treat the table-valued function just like another table and call it from the FROM clause of your SELECT statement. But since the output of your function is just one value, I suggest to retain it as a scalar function. It just needs to be re-written to minimize the extra conditions that are not needed. Also, make sure indexes exist in your CurrencyExchangeRateHistory table on both Casino ID, Currency Code and Transaction Time.

    Regards,
    SQL Server Helper
    SQL Server Helper, Jul 6, 2011
    #4

Share This Page