|
|
Posted by Erland Sommarskog on 09/07/07 21:16
Erland Sommarskog (esquel@sommarskog.se) writes:
> 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.
But that was on SQL 2005. On SQL 2008 (currently in beta) solutions
that used WITH ROLLUP or GROUPING SETS were twice as fast as my naïve
query, and about the same speed as the temp table batch. Also, antother
query that was fairly slow, was just a tad slower than the temp table
on SQL 2008.
> The moral[e]: you know [don't] what is best until you have benchmarked.
Another morale: whatever conclusions you draw about performance applies
only to that version of SQL Server.
--
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
Navigation:
[Reply to this message]
|