September 26, 2008 at 8:21 am
I have a foreign currency rates table which gets multiple updates per second. In some cases when the markets are very busy, the rates are changing so fast that the table will get 40, 50 or more updates per second.
Now the client is upgrading their rate feed, which will be even faster. However, it looks like the initial test showed some database locking issues with the new rate feed.
For starters, I would like to tune my trigger logic to make best use of Inserted/Deleted tables.
I did notice in their logs that some rates are getting duplicated, in which in case I do NOT want my trigger to continue processing (just bail out). So essentially if the currency rate did not change, I want to simply return.
Here's some trigger code we have which I often wonder about.
Please have a look and give me a critique on this, if possible.
alter trigger [b]tr_fxo_rates[/b] on [dbo].[b]fxo_rates[/b]
After Update, Insert
As
declare fxo_rates cursor for
select pkey
from inserted
open fxo_rates
fetch next from fxo_rates INTO @ref_pkey
while @@FETCH_STATUS = 0
begin
select @cur_1 = cur_1,
@cur_2 = cur_2,
@lst_rate = lst_rate,
@lst_bid = lst_bid ,
@lst_ask = lst_ask
from fxo_rates where pkey = @ref_pkey
select @ins_rate = lst_rate, /* var's used to update the main currency table later on... */
@ins_bid = lst_bid ,
@ins_ask = lst_ask
from deleted where pkey = @ref_pkey
/* Execute stored proc in order to update the customer's orders (for @cur_1 and @cur_2)*/
exec fxo_rates_update @cur_1, @cur_2, @lst_rate ,@lst_bid , @lst_ask
fetch next from fxo_rates INTO @ref_pkey
end /* while loop */
/* close/deallocate cursor...return... */
September 26, 2008 at 12:38 pm
The first thing to do, actually, is to get that cursor out of the trigger. It calls a proc, and I'm sure you can make that proc into a set-based command that can take the full data from the inserted and deleted tables and run it all at once. That will, if done correctly, speed the whole thing up massively.
The way to make sure data has actually been changed is to do something like:
select distinct inserted.ID, inserted.value
from inserted
inner join deleted
on inserted.id = deleted.id
and inserted.value != deleted.value;
That will give you just the values that have actually been changed, and will give them to you once per set.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 26, 2008 at 2:48 pm
thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply