Ganapathy, thank you, you are correct. Problem Solved. It would be nice if some indication of trigger activity would be shown in the estimated execution plans in QA.

-Greg


-----Original Message-----
From: Ganapathy S [mailto:[Email Address Removed] Monday, September 08, 2003 10:35 AM
To: LazyDBA.com Discussion
Cc: Greg
Subject: Re: Doubt in QA


There could be an update trigger defined on the table.
The update might be triggering this one and result you see might be due to the trigger.

~Ganapathy

--- Greg <[Email Address Removed] wrote:
> Hello, I'm wondering why these two statements run differently in query analyzer. Firstly, why
> the output of the second statement reports 883600 rows affected _and_ 940 rows affected
> (incidentally there are only 206315 rows in cmDo2). And secondly, why the exec times are so
> different (1 sec vs. 30 sec). If I view the estimated execution plans of the two statements
> together, they are _exactly_ the same, and the cost relative to batch is 50% for each. Indeed
> the underlying rowset being updated is exactly the same. I suppose the difference in exec time
> could have to do with the calculation in the second statement, but the "883600 row(s) affected"
> is worrying me.
>
> Anyone know what's going on and/or what I'm missing?
>
> -Greg
>
>
>
> ** STATEMENT 1 **
>
> UPDATE H SET
> GrossQtyAdj1 = COALESCE(GrossQtyAdj1, 0),
> GrossQtyAdj2 = COALESCE(GrossQtyAdj2, 0),
> GrossQtyAdj3 = COALESCE(GrossQtyAdj3, 0),
> GrossQtyAdj4 = COALESCE(GrossQtyAdj4, 0),
> GrossQtyAdj5 = COALESCE(GrossQtyAdj5, 0),
> MiscAdj = COALESCE(MiscAdj, 0)
> FROM cmDo2 H
> WHERE H.CoNo = 'sc'
> AND H.DoNo IN (SELECT DoNo FROM cmPostBatch2 WHERE BatchId = 1)
> AND H.Serialized = -1
>
> ** OUTPUT **
>
> (940 row(s) affected)
>
> Exec Time ~ 1 sec
>
>
> ** STATEMENT 2 **
>
> UPDATE H SET
> NetQty = GrossQty -
> (GrossQty * GrossQtyAdj1 / 100 +
> GrossQty * GrossQtyAdj2 / 100 +
> GrossQty * GrossQtyAdj3 / 100 +
> GrossQty * GrossQtyAdj4 / 100 +
> GrossQty * GrossQtyAdj5 / 100) +
> MiscAdj
> FROM cmDo2 H
> WHERE H.CoNo = 'sc'
> AND H.DoNo IN (SELECT DoNo FROM cmPostBatch2 WHERE BatchId = 1)
> AND H.Serialized = -1
>
> ** OUTPUT **
>
> (883600 row(s) affected)
>
> (940 row(s) affected)
>
> Exec Time ~ 30 secs.
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional commands, e-mail: mssqldba-[Email Address Removed]


=====
Ganapathy Sankara Baaham
60 Strawberry Hill Ave
Stamford CT USA

If u can dream it,
you can do it.

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
MS Sql Server LazyDBA home page