x

Please can somebody explain how quirky updates work?

I have been reading through Robyn Page's SQL Server Cursor Workbench and she has an example of a "quirky update" like so...

Create tables used.

CREATE TABLE #cb (cb_ID INT IDENTITY(1,1),--sequence of entries 1..n
    Et VARCHAR(10), --entryType
    amount money)--quantity

INSERT INTO #cb(et,amount) SELECT 'balance',465.00
INSERT INTO #cb(et,amount) SELECT 'sale',56.00
INSERT INTO #cb(et,amount) SELECT 'sale',434.30
INSERT INTO #cb(et,amount) SELECT 'purchase',20.04
INSERT INTO #cb(et,amount) SELECT 'purchase',65.00
INSERT INTO #cb(et,amount) SELECT 'sale',23.22
INSERT INTO #cb(et,amount) SELECT 'sale',45.80
INSERT INTO #cb(et,amount) SELECT 'purchase',34.08
INSERT INTO #cb(et,amount) SELECT 'purchase',78.30
INSERT INTO #cb(et,amount) SELECT 'purchase',56.00
INSERT INTO #cb(et,amount) SELECT 'sale',75.22
INSERT INTO #cb(et,amount) SELECT 'sale',5.80
INSERT INTO #cb(et,amount) SELECT 'purchase',3.08
INSERT INTO #cb(et,amount) SELECT 'sale',3.29
INSERT INTO #cb(et,amount) SELECT 'sale',100.80
INSERT INTO #cb(et,amount) SELECT 'sale',100.22
INSERT INTO #cb(et,amount) SELECT 'sale',23.80

DECLARE @cb TABLE(cb_ID INT,--sequence of entries 1..n
    Et VARCHAR(10), --entryType
    amount money,--quantity
    total money)

DECLARE @total money 

SET @total = 0

Now for the quirky update

INSERT INTO @cb(cb_id,Et,amount,total) 
SELECT cb_id,Et,CASE WHEN Et='purchase' 
    THEN -amount 
    ELSE amount 
    END,0 FROM #cb

UPDATE @cb 
SET @total = total = @total + amount FROM @cb 

SELECT [Entry Type]=Et, [amount]=amount, 
    [balance after transaction]=total FROM @cb ORDER BY cb_id

Now why is that a better way of doing things than just taking the original #cb table and doing an inner join like so?

SELECT [Entry Type] = MIN(#cb.Et),  [amount] = MIN(#cb.amount),  [balance after transaction] =  SUM(CASE WHEN total.Et = 'purchase'  THEN -total.amount  ELSE total.amount END)

FROM #cb total INNER JOIN #cb ON total.cb_id <= #cb.cb_id

GROUP BY #cb.cb_id ORDER BY #cb.cb_id
more ▼

asked Feb 17, 2010 at 10:07 AM in Default

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

It is better because it is faster by several orders of magnitude. Just look at the data, and the graph at the end of the section http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/#fourth And you'll see that the triangular join was so slow we couldn't even perform the test when there was a decent number of rows. It doesn't scale
Oct 11, 2010 at 01:40 AM Phil Factor
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

No, I cant. But Jeff Moden can here: http://sqlserverchallenges.com/TSQL_Challenges/Challenge_13/solutions/jeff_moden or here: http://www.sqlservercentral.com/articles/T-SQL/68467/

In essence, I understand it as a section of script that has never produced the wrong results but cant be conclusively proven to always be 100% reliable. IE dont use it in production unless you are running some other benchmark/validation.

more ▼

answered Feb 17, 2010 at 10:22 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

+1 Thanks for the links Fatherjack I will go and have a look at those.
Feb 17, 2010 at 10:30 AM Ian Roke
+1 probably one of the best, mind-blowing articles I have ever read on SSC!
Feb 17, 2010 at 11:03 AM Kev Riley ♦♦
I agree with you there. Jeff Moden has a huge amount of respect from me. Is he on these forums? I have a huge amount of time for what he has got to say.
Feb 17, 2010 at 12:40 PM Ian Roke
Sure is - here he is commenting about the very same subject http://ask.sqlservercentral.com/questions/3017
Feb 17, 2010 at 01:00 PM Kev Riley ♦♦
And you even gave him some advice Ian... ! B).
Feb 17, 2010 at 01:14 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

Holy moly! Thanks for the kudos, folks. With all of the "anti-quirky-update" sentiment going on in the disussion that followed that article, I'm truly humbled by and very appreciative of your comments above. Thank you very much.

Ian, you asked "Now why is that a better way of doing things than just taking the original #cb table and doing an inner join like so?"

SELECT [Entry Type] = MIN(#cb.Et), 
    [amount] = MIN(#cb.amount), 
    [balance after transaction] =
        SUM(CASE WHEN total.Et = 'purchase' 
        THEN -total.amount 
        ELSE total.amount END)

FROM #cb total 
INNER JOIN #cb ON total.cb_id <= #cb.cb_id 

GROUP BY #cb.cb_id ORDER BY #cb.cb_id

That method is known as a "Triangular Join" and the following link leads to an article that explains why that's such a bad thing...

Hidden RBAR: Triangular Joins

--Jeff Moden

more ▼

answered Feb 20, 2010 at 03:02 AM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 4 8

+1 Thanks for the feedback Jeff. Good to see you on these forums! :-)
Feb 20, 2010 at 05:27 AM Ian Roke

Thanks, Ian. Glad to be here but not sure how much time I'll actually spend here. My first love, of course, is SQLServerCentral.com. ;-)

Just curious... did the article on the "Triangular Joins" help in understanding why inequalities in joins have the potential (they're not all bad) for being a server killer?
Feb 25, 2010 at 04:07 AM Jeff Moden
Robyn and I added the timings and a graph to illustrate just how bad the triangular join performed
Oct 11, 2010 at 01:24 AM Phil Factor
(comments are locked)
10|1200 characters needed characters left

IE dont use it in production unless you are running some other benchmark/validation.

Just to be clear on my true postion on the quirky update... In the article I wrote on the subject of running totals and the quirky update, I offered up some validation code to not only prove that the quirky update worked for the examples given but to also give people an additional tool to make them less nervous about using it. In real life, I don't do the checks anymore than someone would write a check to see if their SELECT worked correctly because just like a SELECT, once you get one running correctly and safely (especially in a Temp table), they'll run correctly forever.

Yes, I'm well aware of what people say about the potential for a CU or SP mucking up the works. Two things on that... first, the quirky update has survived all CUs, Hot Fixes, and SPs since before SQL Server was SQL Server. Second, no one in their right mind would blindly install a CU, Hot Fix, or SP without doing full regression testing on all the code. That's the only time I do checks on the quirky update unless a customer specifically asks for other checks.

I'll also say that if folks don't take the time to do the proper research on how to use the quirky update and don't do the proper unit testing (as they should with any code), then they patently shouldn't use the quirky update. "Black Arts" code isn't for casual users by any means. Casual users of T-SQL and people in a hurry should use a well written FORWARD ONLY, READ ONLY, STATIC cursor or a CLR, instead.

--Jeff Moden

more ▼

answered Feb 20, 2010 at 03:35 AM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 4 8

(comments are locked)
10|1200 characters needed characters left
Actually, Paul White came up with a method to ensure that it will always inform you if it goes wrong and, in the process, the "test" forces the code to always do the right thing. I still have a fair bit of testing to do on it buy my immediate feeling is... Paul White for President!
more ▼

answered Oct 10, 2010 at 05:39 PM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 4 8

@Jeff Moden Please count my vote for Paul White for President!
Oct 10, 2010 at 06:25 PM Oleg
(comments are locked)
10|1200 characters needed characters left

Quirky Update was introduced in Sybase because there were no Window Functions then, and there were a whole range of accounting and statistical operations that couldn't be done using the version of SQL that was available at the time.

There are actually two areas of functionality. the first is not controversial at all. Variables were allowed in updates. This is not in the SQL Standard. However, it generally works well, though occasionally one can get oddities happening. The second is to use a variable to accumulate a value, or to carry over a value from a previous row. This relies on an assumption about the order in which SQL Server actually does the Update. In the old Sybase where we used the technique, that was fine, but subsequent optimisation and parallelisation has muddied the waters. This is why Robyn called it 'quirky'.

The technique is fine, if you are very careful with it, and test the results remorselessly over a lot of different sizes of table. Don't let various MVPs bully you into not using it. The reason we have to use it is that SQL Server still hasn't completely implemented the Window functions required by the SQL Standard. When they've done so, we can stop using it!
more ▼

answered Oct 11, 2010 at 01:35 AM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x985
x129

asked: Feb 17, 2010 at 10:07 AM

Seen: 4603 times

Last Updated: Feb 17, 2010 at 10:07 AM