January 18, 2011 at 12:17 pm
We just migrated to sql 2008 R2 from SQL 2000 and the insert/Update trigger that we have on a table is really slow.
It is taking about 30 seconds to update an account now. I have recompiled the trigger but it is still taking long to update
Begin TRAN
Declare
@Discharge datetime,
@DischargeBal money,
@PTNO int
/*allow nested triggers*/
set xact_abort on
update pat
set pat.dis=i.Discharge
from svr1.db1.dbo.Patient_Table pat
inner join Inserted i
on pat.ptno = i.ptno
where (i.Discharge is not null or i.discharge <> ' ')
--if @DischargeBal > 0
update pat
set pat.dis_bal=i.DischargeBal
from svr1.db1.dbo.Patient_Table pat
inner join Inserted i
on pat.ptno = i.ptno
where (i.DischargeBal >0)
Update d
set d.uploaded = '1'
from DischargeTBL d
inner join inserted i
on d.ptno = i.ptno
where d.uploaded <> '1'
IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
Any help would be greatly appreciated! THANKS
January 18, 2011 at 12:44 pm
First using linked servers in a trigger is not a good idea. A better way is to call a sproc on the remote side and let it do any work on the remote end. Cross-server joins may not generate a reasonable plan, I ran into this a while back and I had to go the sproc method. It resolved my issue and actually improved performance substantially.
However with all that said using a linked server in a trigger opens you to a number of problems such as the update (or any other action) will fail if the other server is not available. You might consider service broker if a slight delay is permissible.
CEWII
January 18, 2011 at 12:58 pm
Thank you, the remote proc sounds good to me..will give it a try. Thank you for your time.
January 19, 2011 at 12:21 am
SQL2008 (non R2) had performance issues with linked servers.
It may be worth looking at the R2 cumulative updates for similar fixes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply