Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

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
0
running32
Asked:
running32
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

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.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now