Quirky Update query...works on server ...fails on PC.
-
There is a small modification we can make to avoid the spools and sorts, and get failure-free operation even at 128MB memory:
DECLARE
@LocID INTEGER,
@ProdID INTEGER,
@StkLocProdRunTotal INTEGER = 0,
@RowCounter INTEGER = 1,
@AllRows BIGINT = 9223372036854775807;
;
WITH ToUpdate AS
(
SELECT TOP (@AllRows)
*,
RowCounter =
ROW_NUMBER() OVER(
ORDER BY LocId, ProdId, StkTransDate, TranID)
FROM tempdb.dbo.StockMovements WITH (TABLOCKX)
)
UPDATE ToUpdate
SET @StkLocProdRunTotal =
StkLocProdRunTotal =
CASE
WHEN RowCounter = @RowCounter
THEN
CASE
WHEN LocID = @LocID AND ProdID = @ProdID
THEN @StkLocProdRunTotal + Qty
ELSE Qty
END
ELSE 1/0
END,
@LocID = LocID,
@ProdID = ProdID,
@RowCounter += 1
OPTION (MAXDOP 1);
The key is the extra TOP, which lowers the estimated row count (avoiding sorts and spools) while ensuring all rows are still processed.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Absolutely incredible, Paul. Great bit of research. I've got to find a machine I can make it fail on and then try that fix. Nicely done.
JLS... perhaps you can give Paul's fix a shot on the machine that the QU was failing on?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (1/7/2012)
Absolutely incredible, Paul. Great bit of research. I've got to find a machine I can make it fail on and then try that fix. Nicely done.JLS... perhaps you can give Paul's fix a shot on the machine that the QU was failing on?
The original QU still fails on my PC (3 runs)
Msg 8134, Level 16, State 1, Line 15
Divide by zero error encountered.
SQL Server Execution Times:
CPU time = 55864 ms, elapsed time = 57969 ms.
The statement has been terminated.
Paul's revised QU always completes (3 runs)
Table 'StockMovements'. Scan count 1, logical reads 46473, physical reads 1, read-ahead reads 20353, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(10090000 row(s) affected)
SQL Server Execution Times:
CPU time = 32417 ms, elapsed time = 32491 ms.
Query plan is exactly same as Paul's
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Jeff Moden (1/7/2012)
Absolutely incredible, Paul. Great bit of research...No kidding. What Paul's modestly neglected to mention is the 5-fold performance lift provided by removing the table spool from the plan.
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
ChrisM@home (1/8/2012)
Jeff Moden (1/7/2012)
Absolutely incredible, Paul. Great bit of research...No kidding. What Paul's modestly neglected to mention is the 5-fold performance lift provided by removing the table spool from the plan.
totally agree....some initial tests on a test server had the update run in 17 secs with "new" QU..."old" QU takes 64 secs.
:w00t:
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
J Livingston SQL (1/8/2012)
....some initial tests on a test server had the update run in 17 secs with "new" QU..."old" QU takes 64 secs.Well yes, but I do want to emphasise this is only needed where the clustered index is NOT defined as UNIQUE (as it should be) and the query plan needs to include a sort or an eager spool because of that.
CREATE UNIQUE CLUSTERED INDEX
[IX_StkMove]
ON [dbo].[StockMovements]
(
[LocID] ASC,
[ProdID] ASC,
[StkTransDate] ASC,
[TranID] ASC
)
WITH (DROP_EXISTING = ON)
I would imagine the fields listed *are* guaranteed to be unique in the real world - is that not so?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
SQL Kiwi (1/8/2012)
J Livingston SQL (1/8/2012)
....some initial tests on a test server had the update run in 17 secs with "new" QU..."old" QU takes 64 secs.Well yes, but I do want to emphasise this is only needed where the clustered index is NOT defined as UNIQUE (as it should be) and the query plan needs to include a sort or an eager spool because of that.
CREATE UNIQUE CLUSTERED INDEX
[IX_StkMove]
ON [dbo].[StockMovements]
(
[LocID] ASC,
[ProdID] ASC,
[StkTransDate] ASC,
[TranID] ASC
)
WITH (DROP_EXISTING = ON)
I would imagine the fields listed *are* guaranteed to be unique in the real world - is that not so?
in the "real" world....the TranId will be unique
have to go out now...but will run some more tests this evening
Kind regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
ChrisM@home (1/8/2012)
Jeff Moden (1/7/2012)
Absolutely incredible, Paul. Great bit of research...No kidding. What Paul's modestly neglected to mention is the 5-fold performance lift provided by removing the table spool from the plan.
Let's see... making a toaster-like PC handle something that should be reserved for a much more capable machine and a 5X performance gain to boot?
Mr. White, you've done this several times in the past on these fine forums. I've got to say it again, your ability to read and interpret execution plans and which actions to take to perform improvements is second to none. Very well done.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
SQL Kiwi (1/8/2012)
this is only needed where the clustered index is NOT defined as UNIQUE (as it should be)Ok... now I'm totally embarrassed. I totally missed that very important point in the code. Thanks for bringing it to the surface.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (1/8/2012)
Let's see... making a toaster-like PC handle something that should be reserved for a much more capable machine and a 5X performance gain to boot?Mr. White, you've done this several times in the past on these fine forums. I've got to say it again, your ability to read and interpret execution plans and which actions to take to perform improvements is second to none. Very well done.
Um well that's all very embarrassing (and I think some people inside Microsoft would have something to say about the 'second to none' part!) but thanks all the same :blush:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
SQL Kiwi (1/8/2012)
Jeff Moden (1/8/2012)
Let's see... making a toaster-like PC handle something that should be reserved for a much more capable machine and a 5X performance gain to boot?Mr. White, you've done this several times in the past on these fine forums. I've got to say it again, your ability to read and interpret execution plans and which actions to take to perform improvements is second to none. Very well done.
Um well that's all very embarrassing (and I think some people inside Microsoft would have something to say about the 'second to none' part!) but thanks all the same :blush:
I am one of the "people inside Microsoft," and I second Jeff's observation.
Viewing 11 posts - 46 through 55 (of 55 total)
You must be logged in to reply to this topic. Login to reply