none
Update causing lots of rows to be affected

    Question

  • My update looks like this.  I have a clustered index on adid on the txtad table, so I thought this would affect only a single row.  Instead it takes a while to come back and gives the message below..

    update txtad set filelocation ='http://image.homedepotemail.com/lib/fe6b1570746006757114/m/10/THDC157_2.01.jpg'
    where adid =80563

    (10626 row(s) affected)

    (10626 row(s) affected)

    (1 row(s) affected)

    Why is this update affecting 10626 rows?

    Tuesday, September 24, 2013 12:10 PM

Answers

  • My update looks like this.  I have a clustered index on adid on the txtad table, so I thought this would affect only a single row.  Instead it takes a while to come back and gives the message below..

    update txtad set filelocation ='http://image.homedepotemail.com/lib/fe6b1570746006757114/m/10/THDC157_2.01.jpg'
    where adid =80563

    (10626 row(s) affected)

    (10626 row(s) affected)

    (1 row(s) affected)

    Why is this update affecting 10626 rows?

    I would expect only 1 rowcount message.  It seems you may have a trigger on the table that is affecting other rows.  Note that the presence of a clustered index will affect performance but not the outcome.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, September 24, 2013 12:15 PM

All replies

  • My update looks like this.  I have a clustered index on adid on the txtad table, so I thought this would affect only a single row.  Instead it takes a while to come back and gives the message below..

    update txtad set filelocation ='http://image.homedepotemail.com/lib/fe6b1570746006757114/m/10/THDC157_2.01.jpg'
    where adid =80563

    (10626 row(s) affected)

    (10626 row(s) affected)

    (1 row(s) affected)

    Why is this update affecting 10626 rows?

    I would expect only 1 rowcount message.  It seems you may have a trigger on the table that is affecting other rows.  Note that the presence of a clustered index will affect performance but not the outcome.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, September 24, 2013 12:15 PM
  • thanks for your reply but there is no trigger defined on this table..  I did an sp_depends and there are some stored procs using txtad but not triggers

    Tuesday, September 24, 2013 12:21 PM
  • Could it be because I have non clustered indexes on TxtAd?  I didnt think that would have this effect?
    Tuesday, September 24, 2013 12:22 PM
  • I attach the query plan which to me, suggests it should not affect all those rows - it should just use the adid and update a single row??
    Tuesday, September 24, 2013 12:26 PM
  • What is the result of the query below?

    SELECT COUNT(*)
    FROM txtad
    WHERE adid = 80563;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, September 24, 2013 12:31 PM
  • thanks for your reply but there is no trigger defined on this table..  I did an sp_depends and there are some stored procs using txtad but not triggers

    If abid is unique then the update statement will only affect one records.. the first two record count (10626) are coming from trigger..

    pls run the code bellow

    SELECT * FROM sys.triggers AS t2 WHERE parent_id=object_id('txtad')

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Gert-Jan Strik Tuesday, September 24, 2013 4:45 PM
    Tuesday, September 24, 2013 12:32 PM
  • 1 - as I would expect
    Tuesday, September 24, 2013 12:32 PM
  • If you can tell me how to contact you, I can give you the login to my database and see if you can help me..  I can pay you $50 via paypal to help me?
    Tuesday, September 24, 2013 12:34 PM
  • Can you script out your table structure and share with us? 

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, September 24, 2013 12:34 PM
  • I even just dropped all my non clustered indexes and still getting the same results!
    Tuesday, September 24, 2013 12:35 PM
  • Hello msollicito, sp_depends would not give you trigger info. 

    Could you please try the below query as V.VT suggested and see it it return any rows?

    SELECT * FROM sys.triggers AS t2 WHERE parent_id=object_id('txtad')



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, September 24, 2013 12:49 PM
  • Thank you guys so so much!!  You did point me in the right direction!  It showed there WAS a trigger on the table but I had not created it and it didnt show anywhere - it was created for full text index creation!  I had dropped the full text index a while ago so could not understand why it was still there.

    So I tried to delete it and it wouldnt let me delete the full text index,  it kept saying that the lock timed out - and eventually someone told me that that was because the trigger would be firing as I deleted it so I had to drop the trigger before deleting the full text index!!!

    So thanks - in the end you solved it!!!!

    Wednesday, September 25, 2013 2:09 AM
  • Ok so I spoke too soon!

    it was still really slow after I dropped the full text index and the trigger!!

    So I tried rebuilding the clustered index but got an error

    ===================================

    One or more errors occurred. For details click hyperlink associated with error message. (SqlManagerUI)

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.SqlManagerUI.IndexRebuild.OnRunNow(Object sender)
       at Microsoft.SqlServer.Management.SqlMgmt.PanelExecutionHandler.Run(RunType runType, Object sender)
       at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
       at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
       at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
       at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

    Wednesday, September 25, 2013 2:27 AM
  • Because of the above, I tried deleting the index but that failed too - it gave a lock request timed out error!!
    Wednesday, September 25, 2013 2:28 AM
  • Someone suggested trying it at the command line so I am trying that right now - its taking a long time but at least it has not timed out yet

    alter index PK_TxtAdKey
    on TxtAd
    REBUILD

    Wednesday, September 25, 2013 2:36 AM
  • I had to delete a lot of data, and then the above code DID rebuild the index.. but the query is now not working at all - it just hangs forever!! at least, before, it worked, it just seemed to update a whole lot of other rows too cos of the trigger!!  ;-)  HELP!!

    update txtad
    set filelocation ='http://i.walmart.com/i/email/11/05/28E/Email-Top-Nav-walmart-logo.gif'
    where adid=80851

    Wednesday, September 25, 2013 3:13 AM
  • I tried reorganize the index and it gave me this error

    One or more errors occurred. For details click hyperlink associated with error message. (SqlManagerUI)

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.SqlManagerUI.IndexReorganize.OnRunNow(Object sender)
       at Microsoft.SqlServer.Management.SqlMgmt.PanelExecutionHandler.Run(RunType runType, Object sender)
       at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
       at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
       at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
       at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

    So now trying this from command line

    alter index pk_txtadkey on txtad
    reorganize

    but its taking a while so going to bed

    Wednesday, September 25, 2013 3:15 AM
  • Oh dear, the alter index appeared to work, and I did one update that came back pretty quickly but now they are taking a long time again!

    I wonder if the database is corrupt or something?

    Wednesday, September 25, 2013 12:06 PM