Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Wierd UPDATE


Wierd UPDATE

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24180 Visits: 37951
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10707 Visits: 12006
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

Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24180 Visits: 37951
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24180 Visits: 37951
^%&*^$*^%$*&%(&^%&

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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10707 Visits: 12006
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search