Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Wierd UPDATE Expand / Collapse
Author
Message
Posted Saturday, September 7, 2013 4:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:04 PM
Points: 23,302, Visits: 32,057
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.



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)
Post #1492495
Posted Saturday, September 7, 2013 6:57 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
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
Post #1492524
Posted Saturday, September 7, 2013 7:20 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:04 PM
Points: 23,302, Visits: 32,057
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.



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)
Post #1492526
Posted Saturday, September 7, 2013 7:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:04 PM
Points: 23,302, Visits: 32,057
^%&*^$*^%$*&%(&^%&

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.




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)
Post #1492531
Posted Saturday, September 7, 2013 1:28 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
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
Post #1492549
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse