Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    Arlington, TX
    Posts
    15

    Unanswered: Help with Performance on a Conditional Trigger

    I have a pretty massive conditional trigger. If there is another way of going about this, please let me know. But I'm populating a temp table with records and based on many conditions, I am transforming this data to another table in a corrected format. These conditions I am using reference the final table in many ways, and this seems to become slower and slower as the final table grows larger.

    Take a look and see if you can help me please.
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There are some major issues with your trigger. The length and repetetiveness is just symptomatic.

    When you evaluate statements like:
    IF (SELECT RESV_TOTAL FROM inserted) = 0
    ...you need to keep in mind that the trigger executes once for every insert statement, not once for every inserted record. If you load 100 records into this table the trigger is still going to fire only once, and thus SELECT RESV_TOTAL FROM inserted will return more than one record and can't be compared to the scalar value "0".

    And regarding:
    IF (SELECT COUNT(*) FROM RESERVATION_MASTER RM, inserted
    WHERE RM.BOOK_NO = inserted.BOOK_NO) >= @@ROWCOUNT
    ...I can't even be sure which last successful transaction @@ROWCOUNT refers to, and I'm not convinced you can either.

    I suggest you back off, think about your application process a bit more, and try to write up what you are trying to accomplish as succinctly as possible. Having a clear picture of Point A and Point B often helps in devising the best route from Point A to Point B.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    it certainly does appear that you're not quite consistent in the method of identifying your conditions. but i'd say that can be fixed once you follow blindman's advice. what cannot be fixed is the problem that you addressed us with in the first place, - "it seems to become slower and slower as the final table grows larger."

    if you can revamp your insert process by referencing that final table through a lefty outer join (left that is.) i'd even go further, i'd create a view using that join, and bcp records out. then, i'd drop the trigger all together and just use bulk insert. after all, at that point i only have records that i need.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •