rewrite scalar function to inline table-valued UDFs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

rewrite scalar function to inline table-valued UDFs

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]

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
);        

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
>> 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
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |