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)
BEGIN declare @retval float;
declare @casino_curr_code char(3); set @casino_curr_code =
(SELECT TOP 1 currency_code
FROM Casino
); if (@currency_code = @casino_curr_code) return 1; set @retval = COALESCE(
SELECT TOP 1 exchange_rate
FROM CurrencyExchangeRateHistory
casino_id=@casino_id and
currency_code=@currency_code AND
transact_time <= @end_date
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.
ALTER FUNCTION dbo.getCurrencyExchangeRateByDate (
    @casino_id char(16),
    @currency_code char(3),
    @end_date datetime
            WHEN @currency_code = C.currency_code
            THEN 1.
            ELSE exchange_rate
        END AS ExchangeRate
        CurrencyExchangeRateHistory CER
        Casino C ON CER.casino_id = C.casino_id
        C.casino_id = @casino_id AND
        CER.currency_code = @currency_code AND
        CER.transact_time <= @end_date
        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
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)
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 |