Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««2728293031»»»

Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten) Expand / Collapse
Author
Message
Posted Friday, March 14, 2014 5:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, October 8, 2016 1:59 PM
Points: 263, Visits: 559
Jeff - on behalf of my client

see http://www.sqlservercentral.com/Forums/Topic1550386-392-1.aspx

I took a stored procedure, modified (but not enough) which had used cursors in Oracle that ran for 88 minutes 2 nights ago
last night it ran in 4 minutes
you are so right RBAR is truly evil
Post #1551094
Posted Wednesday, May 28, 2014 8:11 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 18, 2016 2:51 PM
Points: 182, Visits: 774
Has anyone done any significant testing of the Quirky Update method in SQL Server 2008, 2012, or 2014? Does it work in those versions too?

It's easy enough to test out a few million rows myself, but I trust the gurus here a lot more than I trust myself to make any authoritative determination on this.



"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1575418
Posted Wednesday, May 28, 2014 9:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 5,912, Visits: 10,370
autoexcrement (5/28/2014)
Has anyone done any significant testing of the Quirky Update method in SQL Server 2008, 2012, or 2014? Does it work in those versions too?

It's easy enough to test out a few million rows myself, but I trust the gurus here a lot more than I trust myself to make any authoritative determination on this.


http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/


Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1575432
Posted Wednesday, May 28, 2014 10:24 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 18, 2016 2:51 PM
Points: 182, Visits: 774
Thanks, looks like QU is still a winner even in 2012. :)


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1575436
Posted Friday, September 26, 2014 5:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 189, Visits: 540
I came across the Quirky Update initially just last week in one of Phil Factor's "Speed Phreak" challenges, where in the lengthy commentary regarding posted candidate solutions, someone did compare it against the upgraded partition functions in SQL Server 2012, and it's still faster. I wasn't looking for it in particular, but saw "quirky update", and had to find out more!

It's hard to call this undocumented with all its "the evil" connotations people give this, when the syntax for this use of SET is right there in BOL (but not really explained well/in depth). This is a vestige from the mother code from Sybase, where it's probably been a standard, known speed-up for quite some time...
(yes, the syntax for it still in BOL for the UPDATE statement for SQL Server 2014, but no examples)


Post #1620405
Posted Saturday, September 27, 2014 3:08 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:20 PM
Points: 42,081, Visits: 39,470
Seggerman-675349 (3/14/2014)
Jeff - on behalf of my client

see http://www.sqlservercentral.com/Forums/Topic1550386-392-1.aspx

I took a stored procedure, modified (but not enough) which had used cursors in Oracle that ran for 88 minutes 2 nights ago
last night it ran in 4 minutes
you are so right RBAR is truly evil


My apologies. I totally missed this post. Thank you so much for the feedback concerning the "other" world.

Do be advised that Oracle has had the LEAD/LAG functionality, which just came around in 2012 for SQL Server, for quite some time. I probably won't ever test it in Oracle but you should consider using that functionality there.


--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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1620581
Posted Saturday, September 27, 2014 3:16 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:20 PM
Points: 42,081, Visits: 39,470
WayneS (5/28/2014)
autoexcrement (5/28/2014)
Has anyone done any significant testing of the Quirky Update method in SQL Server 2008, 2012, or 2014? Does it work in those versions too?

It's easy enough to test out a few million rows myself, but I trust the gurus here a lot more than I trust myself to make any authoritative determination on this.


http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/


Not just because of the obvious reasons, but I love that post. It's a great comparison of the "new" functionality available in 2012 and clearly demonstrates why one method in 2012 should be used over the other for such things. I've said before but I'll say it again, well done Wayne!


--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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1620582
Posted Saturday, September 27, 2014 3:29 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:20 PM
Points: 42,081, Visits: 39,470
corey lawson (9/26/2014)
I came across the Quirky Update initially just last week in one of Phil Factor's "Speed Phreak" challenges, where in the lengthy commentary regarding posted candidate solutions, someone did compare it against the upgraded partition functions in SQL Server 2012, and it's still faster. I wasn't looking for it in particular, but saw "quirky update", and had to find out more!

It's hard to call this undocumented with all its "the evil" connotations people give this, when the syntax for this use of SET is right there in BOL (but not really explained well/in depth). This is a vestige from the mother code from Sybase, where it's probably been a standard, known speed-up for quite some time...
(yes, the syntax for it still in BOL for the UPDATE statement for SQL Server 2014, but no examples)


Awesome feedback, Corey. Thank you.


--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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1620583
Posted Thursday, October 6, 2016 1:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 23, 2016 7:36 PM
Points: 7, Visits: 32
Hi Jeff
Great article. Came across it when searching for ways to make updates proceed in a sequential order, similar to the running total problem. I have an observation that is contrary to what you stated in the article "...CLUSTERED INDEX MUST BE PRESENT IN THE CORRECT ORDER: ...". I would love it to proceed in the order of the clustered index for my use case but doesn't look like we can. On my test box on MS SQL2012 I find it is running in exactly opposite order to the clustered index & I suspect in random order. Here is a test script and the result of the test script.


set nocount on
go
exec sp_helpindex Party
go
drop trigger upd_trig_Party
go
create trigger upd_trig_Party on Party
for update
as
select 'within update trigger', Party_ID from inserted
go
update Party
set IsDeleted = 1
where Party_ID in (1,2)

--output of above query


index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pk_Party_Party_ID clustered, unique, primary key located on PRIMARY Party_ID



Party_ID
--------------------- -----------
within update trigger 2
within update trigger 1

If the updates in general proceed in the order of the clustered index, my understanding is that the output should have been 1 followed by 2. Possibly I misunderstood your statement or took it out of context. Appreciate if you can clarify. I have verified that the exectuion plan says "Clustered Index Update" on the left most side

Regards
Lakshmi






Post #1824096
Posted Thursday, October 6, 2016 3:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 7,765, Visits: 11,376
lbnayak (10/6/2016)
On my test box on MS SQL2012 I find it is running in exactly opposite order to the clustered index & I suspect in random order. Here is a test script and the result of the test script.


First; There are no guarantees as to the order in which rows are internally processed in an update that affects multiple rows. That is why I rercommend avoiding the quirky update trick, If you are running SQL Server 2012 or higher, then using SUM(xxx) OVER (ORDER BY xxx ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is the supported and guaranteed method to get running totals. If you are on an older version and cannot upgrade, then I would still try to avoid this method. If you really feel that you need to use this method, then at least add the failsafe trick that is suggested earlier in this discussion so that you'll get an error rather than incorrect results if the processing order changes unexpectedly.

Second: Your repro proves nothing. The trigger fires once per statement. The exact order is that first the UPDATE executes (prrocessing all rows, in whatever order SQL Server decides), then the trigger executes, and finally the transaction is committed. The order of rows you see reflects the order in which SQL Server decides to scan the rows from the inserted pseudo-table. This order is unrelated to the order in which rows were updated.

(EDIT: Oh, and can you please edit your post and remove that one-million-dashes line? It messes up the forum's dynamic fomatting)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1824124
« Prev Topic | Next Topic »

Add to briefcase «««2728293031»»»

Permissions Expand / Collapse