1

I have a trigger (MS SQL Server) on TABLE_A that fires on an update that looks basically like the following (I've changed the names of the tables/trigger to simplify):

CREATE TRIGGER [dbo].[TABLE_A_B_UPDATE] ON [dbo].[TABLE_A] 
FOR UPDATE
AS

IF UPDATE ( [ATTRIBUTES] )
BEGIN
    BEGIN TRAN
    -- update the attributes of the table C records
    UPDATE [TABLE_C]
    SET [ATTRIBUTES] = I.ATTRIBUTES
    FROM TABLE_C C
    INNER JOIN INSERTED I ON
        C.UNIQUE_ID = I.UNIQUE_ID AND C.USER_ID = I.USER_ID
    WHERE E.[ATTRIBUTES] & 4 = 0


    UPDATE  [TABLE_B]
    SET     [ATTRIBUTES] = I.ATTRIBUTES
    FROM TABLE_C C
    INNER JOIN INSERTED I ON
        C.UNIQUE_ID = I.UNIQUE_ID AND C.USER_ID = I.USER_ID
    INNER JOIN [TABLE_B] B ON
        B.UNIQUE_ID = C.UNIQUE_ID AND B.USER_ID = C.USER_ID
    WHERE B.[ATTRIBUTES] & 4 = 0

    COMMIT TRAN
END

Currently by design, there is only ever ONE record in the INSERTED table (we are only updating one record at a time from a UI).

What we have discovered is that, as the number of records increase in TABLE_B, trigger performance degrades rapidly. For example, with around 12000 or so records in TABLE_B, this update statement takes around 40 seconds (we established a timeout of 30 seconds). As I remove records from TABLE_B, performance gradually improves. As this was an unacceptable solution, I had to find ways to improve this update statement.

Through testing/profiling, I found that the problem was with the second update statement (update TABLE_B). The first update statement works without problem; if I change the second update statement to its equivalent SELECT statement, it also runs fast.

The solution that I found was to shove the singular record in the INSERTED table into a #TEMP table and join on that instead. I was also able to do this with a table variable as well, but performance was terrible until I created an index on it. This immediately resolved the problem and the update now runs almost instantaneously.

My question is this - why did this solve the performance problem? Perhaps I am looking at this in the wrong way, but I can't imagine why I would need an index on a one record table. I have read that the INSERTED table isn't created with an index on it, but it still seems odd to me that I should need one.

Thanks in advance!

EDIT: As pointed out, I forgot to mention some other relevant table structure tidbits.

TABLE_B indeed has a compound primary key/index created as follows:

CONSTRAINT [TABLE_B] PRIMARY KEY CLUSTERED 
(
    [USER_ID] ASC,
    [UNIQUE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

TABLE_C has the same index as above. All indexes were rebuilt at the start of testing.

Both tables have additional triggers that are being fired - however, during testing, I disabled these triggers to determine where specifically the performance hit was. Disabling all other triggers did not improve performance.

EXECUTION PLAN: I'm not super savvy on execution plans for triggers, but as far as I can tell, you can view them from the profiler with the showplan option turned on. I believe this is the relevant plan:

Execution plan

I'm not sure what to think about this plan, other than I noticed that 80% on that clustered index scan. Hopefully this is what was being asked for, if not, I can repost. Thanks again!

  • Questions about performance are often more efficiently resolved when the execution plan has been provided. Sometimes just a screenshot of the graphical representation will do, at other times the XML format is better. Feel free to do both. (You can upload the XML to a free source code sharing service like Gist or Pastebin and share the link.) – Andriy M May 17 '16 at 13:22
1

as the number of records increase in TABLE_B, trigger performance degrades rapidly

That would imply the updates do not have appropriate indexes available, or are unable to use them for some reason, on TABLE_B or the tables that it is joined to.

Without more detail about the table layout (what indexes you have on each of the tables) and the query plan that SQL has decided to use we can't help you in much detail, so I suggest you update the question with that information.

Some first thoughts:

FROM TABLE_C C INNER JOIN INSERTED I ON C.UNIQUE_ID = I.UNIQUE_ID AND C.USER_ID = I.USER_ID

As you say the performance is tied to TABLE_B this is probably OK, though again the query plan would be helpful information to provide. Presumably you have an index on TABLE_C.UNIQUE_ID or it is the primary key or subject to a unique constraint.

INNER JOIN [TABLE_B] B ON B.UNIQUE_ID = C.UNIQUE_ID AND B.USER_ID = C.USER_ID

With the right indexes/keys this should not be an issue either. For maximum efficient for this specific statement TABLE_C should have an index covering UNIQUE_ID & USER_ID and TABLE_B one covering UNIQUE_ID & USER_ID and including ATTRIBUTES.

WHERE B.[ATTRIBUTES] & 4 = 0

This may be significant. Depending on how TABLE_B and TABLE_C are getting joined (again, provide the index details and query plan) this could be forcing a table scan of TABLE_B. The expense of that operation would grow at linearly with the number of rows in TABLE_B.

  • Thank you for the feedback! I have posted an execution plan - hopefully this is representative of what you are looking for. Let me know if you were talking about something other than that. – RGB May 17 '16 at 17:48
0

as the number of records increase in TABLE_B, trigger performance degrades rapidly.

Which indicates the update statements accessing TABLE_B are inefficient - because of missing indices. Standard debugging will help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.