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 «««2526272829

Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten) Expand / Collapse
Author
Message
Posted Monday, February 13, 2012 11:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:16 AM
Points: 1,920, Visits: 19,336
gcresse (2/13/2012)
I just read this article about SQL Server 2012's new features (http://blog.tallan.com/2011/12/08/sql-server-2012-windowing-functions-part-1-of-2-running-and-sliding-aggregates/) and I'm thinking it *might* solve my problem, as long as I can join to other tables. I had to give up on the Quirky Update because I had to try to jam every column I needed into the same row and in some cases the row was too wide for SQL Server to handle.


whilst you wait for 2012.....could you consider creating a temp table from all of your sub queries / joins etc....and then running QU on the temp table?


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1251362
Posted Monday, February 13, 2012 12:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:56 PM
Points: 77, Visits: 392
The temp table would not be wide enough. I need the ability to calculate running percents on each component in the final product, and often times there are thousands of them.
Post #1251457
Posted Monday, February 13, 2012 12:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:16 AM
Points: 1,920, Visits: 19,336
gcresse (2/13/2012)
The temp table would not be wide enough. I need the ability to calculate running percents on each component in the final product, and often times there are thousands of them.


ok.. I cant see what you can see......but in your situation then I would be asking myself "why have I got too many columns...should I be thinking about rows instead?"...I assume this is what you mean by "not be wide enough"

anyway good luck.

kind regards


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1251466
Posted Monday, February 13, 2012 5:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636
J Livingston SQL (2/13/2012)
gcresse (2/13/2012)
The temp table would not be wide enough. I need the ability to calculate running percents on each component in the final product, and often times there are thousands of them.


ok.. I cant see what you can see......but in your situation then I would be asking myself "why have I got too many columns...should I be thinking about rows instead?"...I assume this is what you mean by "not be wide enough"

anyway good luck.

kind regards


I agree... think rows instead of columns here even if you don't use the QU.

As a sidebar, any process that has thousands of steps should probably be reevaluated a bit.

As another sidebar, can you move the discussion about such a wide table to a new thread, please? Thanks.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1251610
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: Monday, July 7, 2014 7:19 AM
Points: 250, Visits: 537
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: Wednesday, August 20, 2014 5:06 PM
Points: 154, Visits: 584
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: Yesterday @ 10:40 AM
Points: 6,600, Visits: 8,901
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
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: Wednesday, August 20, 2014 5:06 PM
Points: 154, Visits: 584
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
« Prev Topic | Next Topic »

Add to briefcase «««2526272829

Permissions Expand / Collapse