?
Solved

Trigger Running Slow

Posted on 2005-03-22
9
Medium Priority
?
306 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:running32
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 

Author Comment

by:running32
ID: 13605225
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
 
LVL 12

Expert Comment

by:ill
ID: 13605263
/*
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
 
LVL 12

Expert Comment

by:ill
ID: 13605273
local and linked server have same logging strategy?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 13605942

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
 
LVL 34

Expert Comment

by:arbert
ID: 13613530
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
 

Author Comment

by:running32
ID: 13623181
ScottPletcher,  the solution you gave me worked.  Can you tell me why?

thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 13623250
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 13623276
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
 

Author Comment

by:running32
ID: 13623619
Thanks again for your help....  I'll have to remember this one in the future.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question