Rewrite Scalar function as inline table-valued UDF

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Rewrite Scalar function as inline table-valued UDF

Author

Topic

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2011-06-09 : 11:01:06

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

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-09 : 11:04:47

it's a gamble but it is doable. can you provide some sample data please? expected results etc

sample casino table
sample currency_code table
sample CurrencyExchangeRateHistory table

If you don't have the passion to help people, you have no passion

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2011-06-09 : 11:14:02

how do you want this data ? in terms of format

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2011-06-09 : 11:15:40

I have a complex query that runs in 2 seconds without this function and with this function in it runs for 3 mins before i cancelled it, so i know this is the problem

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-09 : 11:16:05

[code]
declare @casino table(id int, name nvarchar(255))
declare @currency_code table(id int, code nvarchar(255))
declare @CurrencyExchangeRateHistory table(id int, currency_code int, rate decimal(10,1), ratedate datetime)

insert into @casino
SELECT 1, 'MGM'
UNION
SELECT 2, 'Venetian'
UNION
SELECT 3, 'The Palazzo'
UNION
SELECT 4, 'Broke as a joke'

insert into @currency_code
SELECT 1, 'USD'
UNION
SELECT 2, 'JPY'
UNION
SELECT 3, 'ZAR'
UNION
SELECT 4, 'CNY'

insert into @CurrencyExchangeRateHistory
SELECT 1, 1, 1, getdate()
UNION
SELECT 2, 2, 103.58, getdate()
UNION
SELECT 3, 3, 7.9576, getdate()
UNION
SELECT 4, 4, 6.9385, getdate()
UNION
SELECT 1, 1, 1, getdate() - 5
UNION
SELECT 2, 2, 103.58, getdate() - 5
UNION
SELECT 3, 3, 7.9576, getdate() - 5
UNION
SELECT 4, 4, 6.9385, getdate() - 5
UNION
SELECT 1, 1, 1, getdate() - 10
UNION
SELECT 2, 2, 103.58, getdate() - 10
UNION
SELECT 3, 3, 7.9576, getdate() - 10
UNION
SELECT 4, 4, 6.9385, getdate() - 10
[/code]

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-09 : 11:25:34

so question for you on what to me seems flaws in the table designs. why is there a currency_code in casino table? Does that mean the casino has one and only one currency_code?

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-09 : 11:38:32

also your function says getCurrencyExchangeRateByDate but returns a boolean. so changing it to table valued function won't benefit you a dime

If you don't have the passion to help people, you have no passion

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2011-06-09 : 11:51:55

INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('9978E0E2-5434-4F9A-A806-00022C79A547',2,'Change Exchange Rate','Apr 17 2010 8:07:48:000PM','SGD',4.718093890068413e-001,'ukgqfpjvuno4rph9','GBP','Apr 17 2010 8:07:48:000PM','jndrs6ezp82zndxt')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('1E621BB8-DD09-46DB-8BD8-0003B41C40ED',2,'Change Exchange Rate','Oct 25 2010 5:47:13:000PM','JPY',1.240000000000000e-002,'9ozxpzxxpuwqf6np','USD','Oct 25 2010 5:47:13:000PM','dzmu7wk3zucrpai7')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('3C1D3806-2177-4594-A07D-00086BB6493A',2,'Change Exchange Rate','Jul 20 2009 8:30:14:000PM','HKD',7.899081336840524e-002,'ukgqfpjvuno4rph9','GBP','Jul 20 2009 8:30:14:000PM','k2hg462r278tb1vs')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('AC12667F-E4CC-40B8-8CA7-000F3F5207A4',2,'Change Exchange Rate','Mar 22 2011 2:46:54:000PM','JPY',1.230000000000000e-002,'9ozxpzxxpuwqf6np','USD','Mar 22 2011 2:46:54:000PM','dzmu7wk3zucrpai7')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('D4673B8A-A347-41E1-8A64-001200E8F846',2,'Change Exchange Rate','Oct 25 2010 8:51:38:000PM','SGD',4.918113411695274e-001,'ukgqfpjvuno4rph9','GBP','Oct 25 2010 8:51:38:000PM','gxecpwukq6sicpiv')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('BF040D3D-9CC9-4EDF-8607-001B5C078880',2,'Change Exchange Rate','Nov 6 2009 8:54:30:000PM','AUD',5.489378053466542e-001,'ukgqfpjvuno4rph9','GBP','Nov 6 2009 8:54:30:000PM','k2hg462r278tb1vs')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('602196E8-6ABB-4632-8C17-001C40DB4B89',2,'Change Exchange Rate','Feb 14 2011 9:09:27:000AM','CZK',4.130620000000000e-002,'so9u4g3g5rc48iu6','EUR','Feb 14 2011 9:09:27:000AM','fhod5505g9t0hm0j')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('5E2835A9-9826-4AE5-B59B-001C971F3EDD',2,'Change Exchange Rate','Mar 2 2010 6:35:04:000PM','UAH',9.220000000000000e-002,'tvtel9zs32ju06nm','EUR','Mar 2 2010 6:35:04:000PM','dzmu7wk3zucrpai7')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('CB235A7A-2EDB-41CF-8DDE-001DC7150CE3',2,'Change Exchange Rate','Jun 9 2011 5:00:02:133AM','PLN',2.253165976976245e-001,'69fqyo823r5yw15y','GBP','Jun 9 2011 5:00:02:133AM','dzmu7wk3zucrpai7')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('CE3F43A8-FC5C-430D-A30F-001EFF75730A',2,'Change Exchange Rate','Feb 12 2010 8:50:06:000PM','CHF',5.990893841361131e-001,'ukgqfpjvuno4rph9','GBP','Feb 12 2010 8:50:06:000PM','3hr85psjtgxxk5sg')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('C7C1037E-4685-4028-ACE6-00212A241918',2,'Change Exchange Rate','May 27 2011 5:00:03:210AM','AED',1.924608204919450e-001,'enmp87ygjhohxkwj','EUR','May 27 2011 5:00:03:210AM','fhod5505g9t0hm0j')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('66A41219-E42F-4F1D-B650-0021F080D88D',2,'Change Exchange Rate','Nov 2 2009 9:11:54:000PM','TRY',4.018000000000000e-001,'q5m6zf7gvz3ku6hr','GBP','Nov 2 2009 9:11:54:000PM','wplgr8g2ivjar5tf')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('21109DA2-C860-45A4-A7AE-0022D911074C',2,'Change Exchange Rate','May 14 2011 5:00:02:163AM','NOK',1.272781418779675e-001,'6kb5fqfah7lqkdfn','EUR','May 14 2011 5:00:02:163AM','dzmu7wk3zucrpai7')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('3018CEEE-7305-4E61-9848-0028E12C82B3',2,'Change Exchange Rate','Mar 29 2011 5:54:12:000PM','TRY',4.550000000000000e-001,'so9u4g3g5rc48iu6','EUR','Mar 29 2011 5:54:12:000PM','dzmu7wk3zucrpai7')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('5FB1434E-8B3E-4AB8-AA37-002F68F928C2 ',2,'Change Exchange Rate','Nov 1 2010 7:04:53:000AM','SEK',9.442112799999999e-002,'69w40i05mizju6jz','GBP','Nov 1 2010 7:04:53:000AM','fhod5505g9t0hm0j')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('79A95BFA-FAAC-45BA-B6C1-003307D7EADB',2,'Change Exchange Rate','Apr 1 2011 4:51:44:000AM','ALL',6.168156000000000e-003,'69w40i05mizju6jz','GBP','Apr 1 2011 4:51:44:000AM','fhod5505g9t0hm0j')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('02C07CE7-6DA4-448D-9662-003446D5EB70',2,'Change Exchange Rate','Apr 7 2010 8:44:45:000PM','USD',6.575918984678109e-001,'ukgqfpjvuno4rph9','GBP','Apr 7 2010 8:44:45:000PM','k2hg462r278tb1vs')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('13538C7F-68BE-4E12-98FD-003534AF0C0E',2,'Change Exchange Rate','Oct 25 2009 9:16:34:000PM','CHF',6.079766536964980e-001,'ukgqfpjvuno4rph9','GBP','Oct 25 2009 9:16:34:000PM','k2hg462r278tb1vs')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('982006E0-F105-4F2D-8E39-003611BD0EF4',2,'Change Exchange Rate','Aug 23 2010 4:28:22:000PM','EUR',1.267300000000000e+000,'9ozxpzxxpuwqf6np','USD','Aug 23 2010 4:28:22:000PM','dzmu7wk3zucrpai7')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('99976DDD-87DC-4929-BD58-00370B2FA10D ',2,'Change Exchange Rate','Apr 1 2011 4:57:22:000AM','RUB',2.202386100000000e-002,'69w40i05mizju6jz','GBP','Apr 1 2011 4:57:22:000AM','fhod5505g9t0hm0j')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('2FEF6BDC-1271-42B6-91E5-003B9C8085A5',2,'Change Exchange Rate','May 3 2010 5:58:43:000PM','CZK',3.387000000000000e-002,'10htpbkfnbuh1is8','GBP','May 3 2010 5:58:43:000PM','dzmu7wk3zucrpai7')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('7719C5C9-4701-4F8C-A364-003E29F9A19B',2,'Change Exchange Rate','May 24 2011 8:37:58:000PM','USD',6.188118811881188e-001,'ukgqfpjvuno4rph9','GBP','May 24 2011 8:37:58:000PM','gxecpwukq6sicpiv')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('72ECB1F4-248C-4CBA-825E-003FEBB7661F',2,'Change Exchange Rate','Jun 1 2011 10:04:50:000AM','BRL',3.763220000000000e-001,'a7l4tzj0ij5b3dck','GBP','Jun 1 2011 10:04:50:000AM','dzmu7wk3zucrpai7')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('219BC5AA-8739-45A0-9C8F-0040917906F4',2,'Change Exchange Rate','Mar 1 2010 11:31:24:000AM','NOK',1.110000000000000e-001,'q5m6zf7gvz3ku6hr','GBP','Mar 1 2010 11:31:24:000AM','fhod5505g9t0hm0j')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('45A0759E-C8F4-41AD-8DE3-0041E1AB0FE6',2,'Change Exchange Rate','Oct 11 2010 6:49:32:000PM','DKK',1.862000000000000e-001,'kk0kjh2foqcyjfw2','USD','Oct 11 2010 6:49:32:000PM','dzmu7wk3zucrpai7')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('7507A460-912B-44ED-B9D2-004489773E5B',2,'Change Exchange Rate','Aug 11 2008 7:14:57:000PM','EUR',7.846000000000000e-001,'ukgqfpjvuno4rph9','GBP','Aug 11 2008 7:14:57:000PM','je3z8ceiv84infge')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('F35BE33B-243C-4EB2-AC5B-004984CF7317',2,'Change Exchange Rate','Jul 8 2010 8:31:47:000PM','JPY',7.560007560007560e-003,'ukgqfpjvuno4rph9','GBP','Jul 8 2010 8:31:47:000PM','k2hg462r278tb1vs')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('7DE107C6-0A2C-4659-96D5-0052C79E1AAC',2,'Change Exchange Rate','Feb 14 2011 9:06:08:000AM','GBP',1.186470000000000e+000,'ctykmg7c0rv32uzd','EUR','Feb 14 2011 9:06:08:000AM','fhod5505g9t0hm0j')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('706F74D2-6131-4C81-A349-0056FEA31145',2,'Change Exchange Rate','May 23 2010 8:55:02:000PM','JPY',7.672889379953808e-003,'ukgqfpjvuno4rph9','GBP','May 23 2010 8:55:02:000PM','k2hg462r278tb1vs')
INSERT INTO [CurrencyExchangeRateHistory] ([record_id],[action_type],[action_desc],[transact_time],[currency_code],[exchange_rate],[casino_id],[base_currency_code],[effect_time],[author_id])VALUES('BDC019F0-8D2F-45F3-8DF4-005F6D44E942',2,'Change Exchange Rate','Dec 30 2010 9:42:03:000AM','HKD',8.291910000000000e-002,'t1zed5i0h9qr3nn0','GBP','Dec 30 2010 9:42:03:000AM','fhod5505g9t0hm0j')

INSERT INTO [CASINO] ([casino_id],[casino_desc],[language_code],[active_flag],[current_version_id],[default_format_id],[default_promo_id],[owner_id],[free_flag],[rowguid],[msrepl_tran_version],[currency_code],[agent_percentage],[junket_conversion_rate],[poker_commission_percentage],[single_currency],[single_country],[country_code],[update_time],[rank_calculate_freq],[Report_Flag])VALUES('0rnu7w1c8xhbuhtn','EG Prototypes','en',1,NULL,NULL,NULL,NULL,1,'C7BD01A0-2509-47FA-AD45-EB3A78E92777','5703A23F-3340-4521-8E72-84C2AFAE70B3','USD',NULL,1.000000000000000e+000,NULL,0,0,'UK',NULL,NULL,2)
INSERT INTO [CASINO] ([casino_id],[casino_desc],[language_code],[active_flag],[current_version_id],[default_format_id],[default_promo_id],[owner_id],[free_flag],[rowguid],[msrepl_tran_version],[currency_code],[agent_percentage],[junket_conversion_rate],[poker_commission_percentage],[single_currency],[single_country],[country_code],[update_time],[rank_calculate_freq],[Report_Flag])VALUES('0tit9xjym7k1m2eh','Supro','en',0,NULL,NULL,NULL,NULL,0,'5A20FFE0-2B4E-4854-A18D-74614943186D','C9148579-973A-42E2-BA87-9A684BF47DB9','GBP',0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,0,0,'AF',NULL,0,3)
INSERT INTO [CASINO] ([casino_id],[casino_desc],[language_code],[active_flag],[current_version_id],[default_format_id],[default_promo_id],[owner_id],[free_flag],[rowguid],[msrepl_tran_version],[currency_code],[agent_percentage],[junket_conversion_rate],[poker_commission_percentage],[single_currency],[single_country],[country_code],[update_time],[rank_calculate_freq],[Report_Flag])VALUES('10htpbkfnbuh1is8','Eurobet','en',1,NULL,NULL,NULL,NULL,0,'0D95FE53-A992-4B4D-B7CC-644BE34732C6','8EC0344C-E389-4246-970F-9BBB2EE69E5C','GBP',0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,0,0,'AF',NULL,NULL,2)
INSERT INTO [CASINO] ([casino_id],[casino_desc],[language_code],[active_flag],[current_version_id],[default_format_id],[default_promo_id],[owner_id],[free_flag],[rowguid],[msrepl_tran_version],[currency_code],[agent_percentage],[junket_conversion_rate],[poker_commission_percentage],[single_currency],[single_country],[country_code],[update_time],[rank_calculate_freq],[Report_Flag])VALUES('1rybgr2mj8rxbdri','Mansion Live Casino','en',1,NULL,NULL,NULL,NULL,0,'F4551B5C-FB32-4075-A884-97583328F3CC','71BB7497-AD8F-41A8-8F97-13BE1FAE1190','GBP',0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,0,0,'GI',NULL,NULL,2)
INSERT INTO [CASINO] ([casino_id],[casino_desc],[language_code],[active_flag],[current_version_id],[default_format_id],[default_promo_id],[owner_id],[free_flag],[rowguid],[msrepl_tran_version],[currency_code],[agent_percentage],[junket_conversion_rate],[poker_commission_percentage],[single_currency],[single_country],[country_code],[update_time],[rank_calculate_freq],[Report_Flag])VALUES('2kl9j8hdmhd9ngtt','888','en',1,NULL,NULL,NULL,NULL,0,'74FCAF01-B047-4437-A3D1-6949A8352B55','B1B433B1-FECD-4FA0-BD64-30D11AC9ADF1','USD',0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,0,0,'IL',NULL,NULL,3)
INSERT INTO [CASINO] ([casino_id],[casino_desc],[language_code],[active_flag],[current_version_id],[default_format_id],[default_promo_id],[owner_id],[free_flag],[rowguid],[msrepl_tran_version],[currency_code],[agent_percentage],[junket_conversion_rate],[poker_commission_percentage],[single_currency],[single_country],[country_code],[update_time],[rank_calculate_freq],[Report_Flag])VALUES('2n7fh6pf7tkoclmv','Little Woods','en',0,NULL,NULL,NULL,NULL,0,'F434900E-77E1-4B07-81C7-FEC32BA8685D','834A40AA-A35B-485D-AE2E-2F5E546CC1CD','GBP',0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,0,0,'AF',NULL,0,3)
INSERT INTO [CASINO] ([casino_id],[casino_desc],[language_code],[active_flag],[current_version_id],[default_format_id],[default_promo_id],[owner_id],[free_flag],[rowguid],[msrepl_tran_version],[currency_code],[agent_percentage],[junket_conversion_rate],[poker_commission_percentage],[single_currency],[single_country],[country_code],[update_time],[rank_calculate_freq],[Report_Flag])VALUES('2ypx2tsuu4k9gt3w','Get Net 777','en',0,NULL,NULL,NULL,NULL,0,'6B86628F-7E13-4476-BB0B-2A8ED5F53312','F0BB006C-04E0-409E-BFA0-7DCE3CE37A88','EUR',0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,0,0,'CY',NULL,0,2)
INSERT INTO [CASINO] ([casino_id],[casino_desc],[language_code],[active_flag],[current_version_id],[default_format_id],[default_promo_id],[owner_id],[free_flag],[rowguid],[msrepl_tran_version],[currency_code],[agent_percentage],[junket_conversion_rate],[poker_commission_percentage],[single_currency],[single_country],[country_code],[update_time],[rank_calculate_freq],[Report_Flag])VALUES('3ceub25ef70g8ser','OffsideBet','en',0,NULL,NULL,NULL,NULL,1,'DCA1E19B-9F6E-41D7-9CE7-C3F08665DAC2','1BC809A0-A5A3-44DA-8089-1A861D5BADB5','EUR',NULL,NULL,NULL,0,0,NULL,NULL,0,NULL)

yes this should have been normalised concerning the currency code, but that is the least of my worries at the moment and impossible to change now.

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2011-06-09 : 11:53:29

nto sure what you mean by it returns a boolean? i thought it was a float

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-09 : 12:03:16

sorry got crossed eyed for a second it does return float. ok how about create table? field data type and all?

If you don't have the passion to help people, you have no passion

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2011-06-09 : 12:20:20

no problem,

CREATE TABLE [dbo].[Casino](
[casino_id] [char](16) NOT NULL,
[casino_desc] [char](100) NOT NULL,
[language_code] [char](4) NOT NULL,
[active_flag] [bit] NOT NULL,
[current_version_id] [char](16) NULL,
[default_format_id] [int] NULL,
[default_promo_id] [char](16) NULL,
[owner_id] [char](16) NULL,
[free_flag] [bit] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
[currency_code] [char](3) NULL,
[agent_percentage] [float] NULL,
[junket_conversion_rate] [float] NULL,
[poker_commission_percentage] [float] NULL,
[single_currency] [bit] NULL,
[single_country] [bit] NULL,
[country_code] [char](2) NULL,
[update_time] [datetime] NULL,
[rank_calculate_freq] [int] NULL,
[Report_Flag] [smallint] NULL)

CREATE TABLE [dbo].[CurrencyExchangeRateHistory](
[record_id] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[action_type] [tinyint] NULL,
[action_desc] [varchar](50) NULL,
[transact_time] [datetime] NULL,
[currency_code] [char](3) NULL,
[exchange_rate] [float] NULL,
[casino_id] [char](16) NULL,
[base_currency_code] [char](3) NULL,
[effect_time] [datetime] NULL,
[author_id] [char](16) NULL)

this is also the offending piece of code, it is calling this function & another function and passing it a value from the Bet table, which is our biggest table by far in the database.

SELECT g.table_id,
COUNT(bet_id) 'bet_count',
count (distinct g.game_id) as 'game_count',
SUM(dbo.getCurrencyExchangeRateByDate(@casino_id,isnull(b.currency_code,u.default_currency_code),g.game_start)*dbo.getInvertCurrencyExchangeRateByDate(@casino_id,@dest_currency_code,g.game_start)*amount) AS 'stake',
SUM(dbo.getCurrencyExchangeRateByDate(@casino_id,isnull(b.currency_code,u.default_currency_code),g.game_start)*dbo.getInvertCurrencyExchangeRateByDate(@casino_id,@dest_currency_code,g.game_start)*payoff) AS 'payoff'
INTO #tmp_betting
FROM Bet b with (nolock,forceseek)
INNER JOIN Game g with (nolock) ON b.game_id = g.game_id
INNER JOIN CasinoUser u with (nolock,forceseek) ON b.user_id = u.user_id
WHERE g.active_flag = 0
and (usertype_code = 'PLAY'
or usertype_code = 'MAGT' or usertype_code like 'SAG%' )
AND b.place_time >= @start_time
AND u.casino_id LIKE @casino_id
GROUP BY g.table_id

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-09 : 12:34:30

yes you will hit some performance ceiling with that. it calls that function for every returned row. so you want it to be table valued function in order to join it to other big table?
make sure you show this to them with execution plan where it is bottle necking.
also how often does the exchange rate change in your database? daily, weekly, monthly? multiple time a day?

If you don't have the passion to help people, you have no passion

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2011-06-09 : 12:47:23

yes we update once a day, to give you an idea, there are about 70 rows in the casino table, 23,000 in exchangeratehistory and 18 million in the bet table!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-09 : 12:52:52

if it is once a day may I suggest a exchange rate history table. That way you do not have to do a dynamic calculation. you get rid of a function and blazing fast response. no function calls for 8 bizillion rows

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-09 : 17:06:07

[code]
CREATE FUNCTION [dbo].[getCurrencyExchangeRateByDate](@casino_id char(16),@currency_code char(3), @end_date datetime)
returns @exr table (exchange_rate float not null)
as
begin

declare @exchange_rate 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)
begin
insert into @exr (exchange_rate) values (1)
end
else
begin
insert into @exr (exchange_rate)
SELECT TOP 1 COALESCE(exchange_rate,0.0)
FROM dbo.CurrencyExchangeRateHistory
WHERE casino_id=@casino_id
and currency_code=@currency_code
AND transact_time <= @end_date
ORDER BY transact_time DESC
end

return
end

GO

[/code]

If you don't have the passion to help people, you have no passion

woodsy1978
Starting Member

8 Posts

Posted - 2011-06-09 : 17:59:14

thanks, how do I edit the code to call the new function ?

am receiving the error when attempting to exec the stored proc that runs the code ....

exec dbo.getCasinoRealTimeBetsStatusInfo_mc'2kl9j8hdmhd9ngtt','2011-06-07','2011-06-08','GBP'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources