Wierd UPDATE

  • Can anyone give me a clue as to what this is about?

    I wrote the following UPDATE statement (Table and column names changed to protect the guilty):

    update dbo.MyTablePublishedReportText set

    TextClassCol = null,

    TextMarkCol = null

    where

    TextClassCol is not null or

    TextMarkCol is not null;

    It is updating the table one row at a time.

    In the same query window of SSMS I have the following code:

    update dbo.MyTableDraftReportText set

    TextClassCol = null,

    TextMarkCol = null

    where

    TextClassCol is not null or

    TextMarkCol is not null;

    It updates the table as a single batch.

    Structurally the tables are identical with the exception of the table names.

    I am confused and could use some help with ideas as to what to look at.

  • Not sure what one row at a time means (or indeed what the relevance of a batch is) here; the specification of T-SQL makes it quite clear that the statement will update all the rows that satisfy the where condition unless prevented/modifed so that only one row is changed by one of two things:

    (a) set rowcount = 1

    (b) a trigger (either an instead of trigger that handles only one of the rows in the inserted/deleted tables, or an after trigger that restores the original value of all but one of the rows affected).

    I imagine you would have noticed if either of those applied, so maybe one row at a time means something else?

    Tom

  • L' Eomot Inversé (9/7/2013)


    Not sure what one row at a time means (or indeed what the relevance of a batch is) here; the specification of T-SQL makes it quite clear that the statement will update all the rows that satisfy the where condition unless prevented/modifed so that only one row is changed by one of two things:

    (a) set rowcount = 1

    (b) a trigger (either an instead of trigger that handles only one of the rows in the inserted/deleted tables, or an after trigger that restores the original value of all but one of the rows affected).

    I imagine you would have noticed if either of those applied, so maybe one row at a time means something else?

    It is updating the table (142,000+ rows) one at a time, literally. As it runs it reports 1 row updated.

    I would look at providing the estimated execution plans for the two queries but I'm not too sure about doing it for security reasons.

    What I have found digging into the two execution plans is this:

    The weird update is using a clustered index scan with an output list of only the TextKey (this is primary and clustered index key). Parallel is set to false.

    The working update is using a clustered index scan with an output list of TextKey, Col1, Col 2 (where Col1 and Col2 are the columns I am trying to set to null). Parallel is true. This one is using a Table Spool (Eager Spool) while the other is not.

    Both, at the end, are doing a Clustered Index Update. The weird update has a cost of 90% for its plane, where the good update has a cost of 0%.

    Again, the tables are structurally identical.

    I'm lost.

  • ^%&*^$*^%$*&%(&^%&

    I figured out it! There is a d*** trigger on the Published table. And guess what, it has embedded while loops.

    Thanks for looking!

    I will disable the trigger, run my update, and then re-enable the trigger.

  • I recall once threating to kick the stuffing out of someone who was tasked to fix an instead of trigger that he had written to assume only one row and "fixed" it by including a while loop; but it was spotted in a desk check, before it ever caused a problem, and I hadn't realised there would be 1 row affected messages each time round the loop inside the trigger.

    Tom

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply