Posted by Erland Sommarskog on 09/06/07 21:15

Erland Sommarskog (esquel@sommarskog.se) writes:
> An interesting challenge to do in one query and only hitting the table
> once, and I was not able to think of a solution without using the
> imaginary Previous() function that we have discussed internally among
> us MVPs.
>
> You can easily do in one query, if you hit the table twice, simply
> one query with GROUP BY store and one with GROUP BY store, month.
> Rank the first and join.
>
> And it's possible to hit the table once, if you first get the
> monthly aggregations into a temp table.
>
> But in a single query? I don't think it's possible is you want the
> format:
>
> Store1, Grand Total, 1000000
> Store1, Jan 2000, 12000
> Store1, Feb 2000, 10000,
> Store2, Grand Total, ....
>
> But I will have to see if I get Itzik Ben-Gan to tackle this one.

Indeed I was, and I also got a suggestion from Steve Kass that you
have already seen, and one from Umachandar Jaychandran, a former MVP
who is now with the Storage Engine team. All three are really bright
guys when it comes to writing queries. Both Umachandar and Itzik
composed query that logically visit the table only once.

Note that the query Steve posted is a cheat: it assumes that you know all
involved subheadings in advance. So in practice, Steve's solution is a two-
pass solution, although retrieving all subheadings is fairly cheap if there
is a non-clustered index on that column.

But as I ran all proposals, save one from Itzik that would require
dynamic SQL to get the subheadings dynamically, I got a big surprise that
calls me to revisit what I said earlier in the thread.

To wit, the fastest solution was one that I wrote that uses a temp
table to hold the subtotals, and then I compute the grand totals from
that one. But the really big surprise was that a fairly naïve query
where I scan the table twice, once for the subtotals and one for the
grand totals was a lot faster than the queries that were one-pass
without using a temp table. The problem they had, is that the execution
plan included a spool operator, that is a worktable introduced by the
optimizer.

The moral: you know what is best until you have benchmarked.

To run the test, I used an inflated version of the Northwind database,
that I call Northgale. You find the script for it at
http://www.sommarskog.se/dynsearch/Northgale.sql.

The test script with the six queries that I tested is attached.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]