Databases
StackOverflow2013
Postsdbo
POUpdate statement in trigger takes longer to execute than executing it outside of trigger
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUpdate statement in trigger takes longer to execute than executing it outside of trigger
    primarykey
    data
    text
    <p>I have a trigger for <code>After Update</code> </p> <p>This trigger works on a server which is going to update 2 tables on a linked server</p> <p>See code </p> <pre><code>ALTER TRIGGER [dbo].[tgAfterUpdate] ON [dbo].[KS_3EToVision] AFTER UPDATE AS BEGIN BEGIN TRY BEGIN TRANSACTION -- Start the transaction DECLARE @MatterNumber varchar(15); SELECT @MatterNumber = i.MatterNumber FROM inserted i; IF EXISTS(SELECT * FROM [FSSQLDEV01].[collnab].[dbo].[collection_header] WHERE Ch_file_number COLLATE DATABASE_DEFAULT = @MatterNumber COLLATE DATABASE_DEFAULT) --UPDATE IF (SELECT Ch_matter_status FROM [FSSQLDEV01].[collnab].[dbo].[collection_header] WHERE Ch_file_number COLLATE DATABASE_DEFAULT = @MatterNumber COLLATE DATABASE_DEFAULT ) = 'Current' BEGIN --Parent table UPDATE [FSSQLDEV01].[collnab].[dbo].[collection_header] SET Ch_start_date = i.OpenDate FROM Inserted i WHERE ch_file_number COLLATE DATABASE_DEFAULT = @MatterNumber COLLATE DATABASE_DEFAULT ----Child table UPDATE [FSSQLDEV01].[collnab].[dbo].[collections] SET Defendant_1 = i.Description , Loan_Number_1 = i.Comments , Client = i.KS_BookName , Date_Instructed = i.OpenDate , Person_Responsible_name = i.ResponsibleFeeEarnerName , Person_Responsible_Email = i.ResponsibleFeeEarnerEmail , Person_Acting_name = i.BillingFeeEarnerName , Person_Acting_email = i.BillingFeeEarnerEmail , Agent_Acting_name = i.BillingFeeEarnerName , Agent_Acting_email = i.BillingFeeEarnerEmail , CBA_Panel_Service_Area = i.KS_ServiceCat , HBN_Number = i.KS_ClientAcctRef , St_George_Contact = i.KS_Instructor FROM Inserted i WHERE Left(Collections.file_number,6) COLLATE DATABASE_DEFAULT = @MatterNumber COLLATE DATABASE_DEFAULT END --If we reach here, success! COMMIT END TRY BEGIN CATCH -- Whoops, there was an error IF @@TRANCOUNT &gt; 0 ROLLBACK -- Raise an error with the details of the exception DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY() RAISERROR(@ErrMsg, @ErrSeverity, 1) END CATCH END </code></pre> <p>if i try the update statement with the same criteria outside of the trigger it will only take 1 second but inside the trigger it can take upto 45 seconds.</p> <p>I have narrowed it down to the second update statement being the problem, because if i remove the second update statement it will execute quickly.</p> <p>I have also attached an image the shows </p> <p>I also have a Trigger for Insert and this works quickly within a second And one for Deleting which will take an extended period with in the trigger but functions fine if i remove the 2 delete statements outside of the trigger.</p> <pre><code>USE [TE_3E_TRG] GO /****** Object: Trigger [dbo].[tgAfterDelete] Script Date: 08/30/2012 11:24:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[tgAfterDelete] ON [dbo].[KS_3EToVision] AFTER DELETE AS begin BEGIN TRY BEGIN TRANSACTION -- Start the transaction Begin --Parent table DELETE FROM [FSSQLDEV01].[collnab].[dbo].[collection_header] WHERE ch_file_number COLLATE DATABASE_DEFAULT = (SELECT d.MatterNumber FROM deleted d) COLLATE DATABASE_DEFAULT --Child table DELETE FROM [FSSQLDEV01].[collnab].[dbo].[collections] WHERE Left(file_number,6) COLLATE DATABASE_DEFAULT = (SELECT d.MatterNumber FROM deleted d) COLLATE DATABASE_DEFAULT END -- If we reach here, success! COMMIT END TRY BEGIN CATCH -- Whoops, there was an error IF @@TRANCOUNT &gt; 0 ROLLBACK -- Raise an error with the details of the exception DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY() RAISERROR(@ErrMsg, @ErrSeverity, 1) END CATCH </code></pre> <p>Does anyone know why this drastic difference in execution time may occur between executing the statements within the trigger and out side of the trigger?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USjohn.g.enseer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUpdate statement in trigger takes longer to execute than executing it outside of trigger
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUpdate statement in trigger takes longer to execute than executing it outside of trigger
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSide-note: this line ` SELECT @MatterNumber = i.MatterNumber FROM inserted i; ` right at the beginning of your trigger will fail **horribly** when your `UPDATE` statement should ever affect more than one row. The trigger is fired **once per statement** and both the `Inserted` as well as `Deleted` pseudo tables might contain **multiple rows** in such a case. You can never rely on the fact that the `Inserted` table only contains a single row!
      singulars
      1. POUpdate statement in trigger takes longer to execute than executing it outside of trigger
      1. USmarc_s
    2. COPossibly the issue is that the remote tables are modified within a single transaction (which thus becomes a distributed transaction), and that probably adds some overhead. I'm far from being sure, though, and hope I'll be corrected if I'm utterly wrong.
      singulars
      1. POUpdate statement in trigger takes longer to execute than executing it outside of trigger
      1. USAndriy M
    3. COOf course, the fact that you are applying a function (`LEFT()`) to a column in the WHERE clause doesn't help matters either.
      singulars
      1. POUpdate statement in trigger takes longer to execute than executing it outside of trigger
      1. USAndriy M
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload