Trigger Running Slow

I have a trigger that is running really slow.  It takes about 16 seconds to update the linked server table.  

I need to update the linked table as soon as the table on the local server is updated.  How can I speed this up?

Thanks
 

Create Trigger Consent_Date
ON tblPatient
FOR UPDATE
AS
IF UPDATE(dtmEligExpires)
SET XACT_ABORT ON
UPDATE    BCSQL.dentrix.dbo.DDB_PAT_BASE
SET              CONSENTDATE = tblpatient.dtmEligExpires
FROM         tblpatient JOIN
                      BCSQL.dentrix.dbo.DDB_PAT_BASE ON chart = tblpatient.strPatientid
WHERE  chart = '900000' --tblpatient.strPatientid
running32Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

running32Author Commented:
If I update the local copy it fires instantly.  But if I update the local copy and then fire a trigger from the local copy the application is still hung for about 15 seconds.

Thanks
0
illCommented:
/*
if not all record are upated, you can limit it. also make sure, that chart ,strPatientid are same datatype in join.
FROM         tblpatient JOIN
 ->
FROM         inserted JOIN
*/
Create Trigger Consent_Date
ON tblPatient
FOR UPDATE
AS
IF UPDATE(dtmEligExpires)
SET XACT_ABORT ON
UPDATE    BCSQL.dentrix.dbo.DDB_PAT_BASE
SET              CONSENTDATE = tblpatient.dtmEligExpires
FROM         inserted JOIN
                      BCSQL.dentrix.dbo.DDB_PAT_BASE ON chart = tblpatient.strPatientid
WHERE  chart = '900000' --tblpatient.strPatientid



0
illCommented:
local and linked server have same logging strategy?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:

Create Trigger Consent_Date
ON tblPatient
FOR UPDATE
AS
IF UPDATE(dtmEligExpires)
SET XACT_ABORT ON
UPDATE    BCSQL.dentrix.dbo.DDB_PAT_BASE
SET              CONSENTDATE = inserted.dtmEligExpires
FROM BCSQL.dentrix.dbo.DDB_PAT_BASE
INNER JOIN inserted ON BCSQL.dentrix.dbo.DDB_PAT_BASE = inserted.strPatientid
WHERE inserted.chart = '900000' --tblpatient.strPatientid
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arbertCommented:
Like I said in your other question:

If you just look at the query plan for this in query analyzer, what does it show:

UPDATE    BCSQL.dentrix.dbo.DDB_PAT_BASE
SET              CONSENTDATE = tblpatient.dtmEligExpires
FROM         tblpatient JOIN
                      BCSQL.dentrix.dbo.DDB_PAT_BASE ON chart = tblpatient.strPatientid
WHERE  chart = '900000' --tblpatient.strPatientid



My guess is, an index isn't being used on the remote update statement.....
0
running32Author Commented:
ScottPletcher,  the solution you gave me worked.  Can you tell me why?

thanks
0
Scott PletcherSenior DBACommented:
I think the original statement was updating every row on the original table that had a chart = '900000'.  

In the new version, it should update only the row(s) updated for that particular run of the trigger.  That is, only the rows that were in the original UPDATE statement are UPDATEd by the trigger.
0
Scott PletcherSenior DBACommented:
That's because I referred to the inserted table rather than the tblPatient table.

The inserted "table" [pseudo-table really] contains only the rows affected by the statement that caused the trigger to fire.  Since this is an update trigger, only the rows actually updated are in the inserted table.
0
running32Author Commented:
Thanks again for your help....  I'll have to remember this one in the future.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.