John Walker (1/27/2009)

---

I think Jeff hinted that he was going to eventually get to the analytics solution and far be it from me to steal his thunder, but I couldn't help wondering if either I have misunderstood the scale of the problem or the solutions displayed thus far have been grossly convoluted. From my understanding of the problem at its simplest, we would like to compute a running total (leaving any notion of partitioning aside for the moment). Surely all that is needed is something like this:

select AccountId,
Date,
Amount,
sum(Amount) over (partition by AccountId, order by Date) as "Running_Balance"
from JBMTest
order by AccountId, Date

Now, partitioning. What has that got to do with anything? Again, from our simple perspective we don't care. Let our optimizer handle such details. As the query stands, we're looking to cover the entire table anyway. Given that our partitions are based on date, if we restrict to one year, we touch only one partition - but that is not something we need to explicitly state in a hint or some such construct, surely.

peter (1/27/2009)

---

Not having access to a developer or Enterprise version of SQL Server I find this topic hard to go into myself. Some of the SQL features used are only available on those versions for those wondering why I bring it up. Still I did quickly read trough the essential parts that make up the solution and the path followed to get there and got a bit of a bad feeling after reading it.

Searching for working methods is one thing, but what bugs me in this case is that the final output depends on query hints like OPTION (MAXDOP 1) that technically should not affect the output at all. The solution is relying on SQL Server implementation details that are unspecified by microsoft and are likely to change over time.

Even when implementation details do not change, maintainability is at risk because the technique breaks with the very useful concept of SQL itself. You ask a question and specify what information you want as output, in what order. It is then up to the database to deliver according to your specifications as best as it can, isolating you from the details on how it does it.

Imagine using the described technique and 4 years from now you move to a 4 processor, 12 core system and solid state disks. There turns out to be unexpected low performance and an expert is hired to see what is wrong. First thing that would get the boot would be the OPTION (MAXDOP 1). It would speed the result significantly and since such a hint SHOULD NEVER affect output it is unlikely to be noticed right away.

You see where I am going with this....it feels like playing dice with your data!

I wish I had a true single processor system (no hyper threading or multiple cores) to have tested this on to see how it worked. Would I have thought that this worked without MAXDOP 1 in that environment? If it did, what would my reaction have been when I moved it to multi-processor, multi-core environment and it suddenly failed (remember, I did a final test in such an environment)?

Smendle (1/27/2009)

---

Oh but you do! Go into your BIOS and turn that stuff off!

noeld (1/27/2009)

---

I liked your article ( a lot ) and it seems that you put a lot of effort into it.

My view of running totals is simple. It is a CLIENT-SIDE responsibility.

Although it may provide you with an alternative to deliver to the developers what they may want out of the DB
IMHO looping through an ordered result set in the client is by far more scalable and "faster" end-to-end from the User point of view.

Just my $0.02

Why is the CPU time greater than the elapsed time

CPU time = 5063 ms, elapsed time = 3309 ms.