SQL Server Performance

Trigger

Discussion in 'T-SQL Performance Tuning for Developers' started by tserva, Sep 20, 2005.

  1. tserva New Member

    I hope this is not too stupid, but I have a trigger that is supposed to update the office field when the user field is changed. BUT, my trigger updates all of the office fields for all of the users when any one of the users are updated.
    How do I update only the record that was just updated?





    CREATE TRIGGER [Commission1Office] ON dbo.Relo_Referrals
    After UPDATE
    AS
    IF Update (Comm1AgtID)
    Update Relo_Referrals
    Set Comm1OffcID = Offices.office_id
    FROM Users INNER JOIN
    Relo_Referrals ON Users.ID = Relo_Referrals.Comm1AgtID LEFT OUTER JOIN
    offices ON Users.LOCATION = offices.officeid

  2. dineshasanka Moderator

  3. FrankKalis Moderator

    Yes, I would also say, you are missing some JOIN to the inserted pseudo table. That way now you are about to always UPDATE all rows with your trigger.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  4. Adriaan New Member

    Problem with dineshashanka's solution is that it doesn't cover the trigger firing when more than one row is being updated, so follow Frank's suggestion to add the "inserted" pseudo table:

    CREATE TRIGGER [Commission1Office] ON dbo.Relo_Referrals
    After UPDATE
    AS
    IF Update (Comm1AgtID)
    Update Relo_Referrals
    Set Comm1OffcID = Offices.office_id
    FROM Relo_Referrals
    INNER JOIN inserted ON Relo_Referrals.<identity_column> = inserted.<identity_column>
    INNER JOIN Users ON Users.ID = Relo_Referrals.Comm1AgtID
    INNER JOIN offices ON Users.LOCATION = offices.officeid

    I'm assuming that Relo_Referrals has an identity column, else you'll need to join on all fields of the primary key.
  5. kramer New Member

    I was just about to post something about the "inserted" temp table when i read this post so thought i should put my comments here.
    In answer to the posted question you can refer to the updated trips when using a trigger by a join between the Updated Table and the "inserted" temp table which is a sub set of the data that has been changed
    Hence

    CREATE TRIGGER [Commission1Office] ON dbo.Relo_Referrals
    After UPDATE
    AS
    IF Update (Comm1AgtID)
    Update Relo_Referrals
    Set Comm1OffcID = Offices.office_id
    FROM Users INNER JOIN
    Relo_Referrals ON Users.ID = inserted.Comm1AgtID LEFT OUTER JOIN
    offices ON Users.LOCATION = offices.officeid


    My comment was going to be I find using the inserted table tends to be impair performance of queries ... in execution plans the majority of the query, when the logic dictates it should use the inserted table, will be taken up with accessing the inserted table.
    is there any tweaks or different approaches when using the inserted table?
    I look forwad to your comments.
  6. Adriaan New Member

    Why use an outer join? Only necessary when you want to update to null in case there is no match. Inner joins are usually 'quicker' than outer joins.

    [EDIT:]... and of course UPDATE queries are just plain slower than SELECTs.
  7. kramer New Member

    Adriaan
    If you are referring to the code I posted I simply copied the first authors code and switched in the inserted table. I agree though , the outer join does look a little out of place.
  8. FrankKalis Moderator

    I think it would have been better when you've started a new thread. Your question is related to this topic here, but certainly different. Anyway...

    Why do you think accessing the inserted table drags down performance? Generally speaking, you would want to keep your trigger logic as simple as possible. If you code complex logic in triggers, you ask for performance problems. Can you give us an example?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  9. kramer New Member

    the system in question is a real time warehouse operation
    Here is a sample of the trigger

    -- verify all conditions for this update are present
    SELECT job_no,trp_trip_no FROM inserted
    join jobs ON TRP_Job_No = JOB_No
    WHERE JOB_Type = 'K'
    and TRP_Status = 'I'
    and TRP_Cartons > 0

    if @@rowcount = 1


    if the rowcount is met then i will update a number of other tables....
    the subsequent updates don't seem to be the problem however using the inserted table even when the condition is not met takes considerably longer the update which triggered it.. at least on the execution plan that is.
    I was wondering whether there is somekind of trigger caching configuration i have failed to set?

  10. Adriaan New Member

    Your query doesn't perform a join operation, but it is returning a cartesian product between (a) all rows in inserted, and (b) all rows from your JOBS table that match the criteria in the WHERE statement.

    Your query returns (a)*(b) rows, instead of (a) rows.

    Please look up the proper syntax for JOIN.

    Hint #1: you need to join inserted and JOBS on the key field(s) in order to reduce the number of rows.

    Hint #2: at the start of your trigger, before any other statement is executed, you can use @@ROWCOUNT to ascertain the number of rows affected by the event that fired the trigger.
  11. kramer New Member

    I am not sure i understand you adrian.

    the field JOB_no is the PK in the table JOBS
    The field trp_job_no is the FK on the table inserted represents.
    I will thus return the rows from inserted and jobs which match and where the where clause is matched...

    My understanding is that this will not produce a cartesian.
    I stand to be corrected.

    K
  12. Adriaan New Member

    Kramer,

    No problem, I wasn't reading close enough ... If I may so bold as to give you another hint - use table names or aliases with each column in the query, it makes it a lot easier to understand. Plus it can actually help improve execution time!

    You might try adding the FK table as well, JOINed on inserted, as I'm not sure there are any indexes available when you refer to inserted by itself.
  13. kramer New Member

    I take your point as regards using an alias.

    I too am unsure as to whether inserted uses an index, in fact i am pretty sure it does not.
    However i do not know how to include the FK table in the manner you are suggesting without using the Inserted table.
    Can you expand on the technique you would use?
  14. Adriaan New Member

    Just use regular joins:

    SELECT inserted.*
    FROM inserted
    INNER JOIN FKTable ON inserted.Key = FKTable.Key
    INNER JOIN Jobs ON FKTable.Key = Jobs.ForeignKey

    I would assume that inserted does 'have' a primary key, so joining it to the underlying table on the key column(s) should not present a problem.
  15. indshri Member

    Kramer -- After the join you are checking if @@rowcount = 1 . So what will happen if the join fetches lot of rows. Correct me if I am wrong but I think you may have lot of rows. Seems you want to check if there is only one row that meets the criteria.
    Use Top 2 in your join and then see what happens.
  16. indshri Member

    Kramer -- I again read your query and in comments you say that "Verify if all the conditions are met " , So i think you may want to use if exists ( select 1 from ..... ) if I am correct.

Share This Page