One of the things that I’m the most excited about in “Denali” CTP3 is the enhancements to the OVER clause. One of the things that I’m particular excited about is the addition of the ROWS/RANGE clause support. And one of the reasons that I’m excited about this is that this provides a supported, documented (in BOL) way to perform running totals.

My current favorite method of performing running totals is using the so-called “Quirky Update” / “Pseudo-Cursor” method. However, there is plenty of controversy surrounding the use of this method, primarily centered around the need for following a list of rules – rules that are not documented in BOL. (Please see this article for how to perform running totals using the Quirky Update method, and for the rules necessary to achieve the proper results.)

Now that running totals are supported in “Denali” CTP3, I decided to test out the various methods of doing so. Using the environment setup and test code from the above article, I added in the various methods of doing running totals available in “Denali” CTP3. (Code reprinted with permission of Jeff Moden.)
[codesyntax lang=”tsql” capitalize=”no”]

[/codesyntax]
And the results, as shown from Profiler, are:
(Click image to view the full Profiler screen.)

When performing the running totals with the new OVER clause enhancements in “Denali”, it has 3 million to 18 million reads, vs. 8 thousand for the Quirky Update. This difference is reflected in the time each takes: the OVER clause enhancements are taking 22 – 75 seconds to perform the running totals, while the Quirky Update performs it in 3.5 seconds.

Let’s hope that all of this is due to it being in a beta state, and that it will improve when it reaches RTM. When it does reach the RTM status, I’ll test again to decide which method I’ll use. My impression right now is that it looks like I’ll be keeping the Quirky Update in my toolbag for a long time when performing running totals.

Update: 2014-01-30

Well, it’s been a while since SQL 2012 went to RTM, so I finally decided to rerun this performance test. I’ve waited so long, it’s actually running on SQL 2012 SP1. (And, my hardware has been upgraded, which you will notice with the improved times). The 2012 performance results are:

TextData CPU Reads Writes Duration Rank by Duration
– Quirky Update running totals 1623 8764 0 1675 1
– SQL 2012 SP1 running totals using DEFAULT RANGE option 24773 17943842 4005 26018 7
– SQL 2012 SP1 running totals subquery using DEFAULT RANGE option 25334 17943846 4013 26556 8
– SQL 2012 SP1 running totals with self-join using DEFAULT RANGE option 23289 12053268 4159 17226 4
– SQL 2012 SP1 running totals using ROWS UNBOUNDED PRECEDING 13120 8902436 0 13714 2
– SQL 2012 SP1 running totals using RANGE UNBOUNDED PRECEDING 24897 17943908 4125 25945 6
– SQL 2012 SP1 running totals using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 13292 8902440 0 13930 3
– SQL 2012 SP1 running totals using RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 24461 17943851 4056 25899 5
– SQL 2012 SP1 Running Totals using ROWS BETWEEN 2147483647 PRECEDING AND CURRENT ROW 27971 17882461 10224 29210 9

As you can see, the Quirky Update still blows the other methods away. However, the OVER() clause enhancements are still way better than a cursor, or even a self-join. So, my recommendation is to use the OVER() clause, unless you need the extra performance boost of the Quirky Update. This is based solely upon the need to follow undocumented rules for the Quirky Update. Testing will reveal which method you need to use.

Something that you may notice in the results: all of these tests are updating a table with the running total. So how is it that the Quirky Update and the next two fastest means have zero writes? I’ve noticed this before and I filed a connect item about it, which Microsoft has decided that they won’t fix (not enough people affected). If you would like to see this fixed, please go vote for this connect item.