Running Total Performance

Question

Running Total Performance

Kathi Kellenberger

SSChampion

Points: 11811
More actions
April 2, 2015 at 11:17 pm

#316998

Comments posted to this topic are about the item Running Total Performance
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Carlo Romagnano SSC-Insane Points: 22662 More actions · Answer 1

Kathi Kellenberger (4/2/2015)
Comments posted to this topic are about the item <A HREF="/questions/Running+Totals/124109/">Running Total Performance</A>

Thx, very interesting.

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 2

Interesting question but maybe too easy with some minutes to spend :

- Plenty of RAM : too easy to be the good choice

- to use OPTIMIZE : never seen this parameter in the OVER clause

- to use older methods : surprising

only one left choice

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Ran into this issue myself some months ago. Even wrote a blog post about it (shameless plug ahead!):

Beware the defaults! (in windowing functions)[/url]

The book by Itzik describes this behaviour in great detail: it's because ROWS can use a specialized in-memory table for temporary results, but RANGE cannot and has thus to use the slower disk option.

Funny is that a colleague of mine ran into this issue today and I just helped her solve it 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 4

There is no OPTIMIZE in the OVER clause. It was just a wrong answer. So, the older methods is something that people might think of because window aggregates without an ORDER BY in the OVER clause sometimes do not perform as well as older methods.

Thanks,

Kathi

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 5

Itzik's book on windows functions is fantastic, and I have learned a lot from him. ROWS does use an in-memory table most of the time, but not in every case. When it does use an in-memory table, the performance is great.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Kathi Kellenberger (4/3/2015)
ROWS does use an in-memory table most of the time, but not in every case.

Yeah, there are some conditions that have to be met, such as the window frame extents, but I didn't memorize them. 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

kslentz Say Hey Kid Points: 690 More actions · Answer 7

https://www.simple-talk.com/sql/t-sql-programming/the-performance-of-the-t-sql-window-functions/

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 8

Koen Verbeeck (4/3/2015)
...
The book by Itzik describes this behaviour in great detail: it's because ROWS can use a specialized in-memory table for temporary results, but RANGE cannot and has thus to use the slower disk option....

Thank you for that book advice.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 9

Thank you for post, really good one. I have theoretically read about this but never actually used it at all.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 10

Be sure to check out my new book as well!

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 11

Even with the correct answer, I'm still disappointed in the performance. MS really could have done a better job on this particular subject.

--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)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 12

Kathi Kellenberger (4/3/2015)
Be sure to check out my new book as well!

I might. Is there anything in there that is not in Itzik's book?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 13

There are a few things, such as creating a custom window aggregate function. Itzik's book spends a lot of time talking about theoretical functionality that I don't. I also have a great chapter written by BI guru Clayton Groom that shows how to use window functions instead of SSAS cubes. My teaching style is different than Itzik's as well.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 14

Jeff -- For the running totals functionality, I think the performance is pretty good if you use ROWS and have the correct index in place. Where I really wish they did things better is windows aggregates without the ORDER BY in the OVER clause.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor