Trigger Inserted and Updated Table RRS feed

  • Question

  • Hi Team,

    I have a vendor application where a table with 1 million rows get updated without a where condition (all rows). This table is having an update/insert/delete trigger.

    Inside the trigger there is TQL statement as below

     Select @NLCount=Count(*) from deleted where NLstatus=1

    The update statment is taking 5 to 6 hours, stuck in the above statement with in the trigger.

    can somebody help me please?

    if i disable the trigger, the update finish in 7 mins

    Wednesday, February 6, 2013 2:06 PM


All replies

  • I think you need to talk to the vendor, how do you want us to help you? If you can disable the trigger so go for it...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Olaf HelperMVP Wednesday, February 20, 2013 6:51 PM
    • Marked as answer by Fanny Liu Friday, March 8, 2013 4:39 AM
    Wednesday, February 6, 2013 2:23 PM
  • thanks for the response,

    is there a way to tune the statement

    Select @NLCount=Count(*) from deleted where NLstatus=1

    Wednesday, February 6, 2013 2:56 PM
  • What version of SQL server is this?


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, February 6, 2013 2:58 PM
  • Select @NLCount=Count(*) from deleted where NLstatus=1

    "Deleted" is a virtual table; without any index so such a query can take a long time, when huge amount of rows are updated.

    As Uri wrote, you should contact the vendor that he has to solve this issue. Without knowing the application no one can tell, which issues will cause disabling the trigger.

    Olaf Helper

    Blog Xing

    Wednesday, February 20, 2013 6:50 PM