| Groups | Blog | Home |
sql server (alternate) : Increasingly greater amounts of redundant aggregate data in queries?
I'd like to start by saying I'm not a DBA, rather I am a Java developer. But I'm learning more and more about database design, and it's very interesting. Here is the general issue I'm facing: Which one is better? 1. To perform aggregate queries on data, and use that data to home in and perform more detailed analysis? This gives result sets that are potentially filled with columns that contain the aggregate data repeated across many rows. 2. To perform the aggregate query once, and then hit the database again for a detailed analysis, and return the detailed analysis results in an order that is easily cross-correlated with the original aggregate data? This means running the aggregate query essentially twice, just the second time it is hidden as a subquery. Now I realize this is a very general question, and I have made assumptions that a junior DBA does (e.g. in the second option, the aggregate query must be run again - perhaps this is false?). Do I need to give an example? It seems to me that you senior DBA's would run across this a lot; I myself am finding these issues as they crop up very fascinating. Thanks for any opinions in advance. I will be happy to give an example if needed. - C
[quoted text, click to view]
On Sep 2, 6:18 pm, csi...@gmail.com wrote: > Hello, > > I'd like to start by saying I'm not a DBA, rather I am a Java > developer. But I'm learning more and more about database design, and > it's very interesting. > > Here is the general issue I'm facing: > > Which one is better? > 1. To perform aggregate queries on data, and use that data to home in > and perform more detailed analysis? This gives result sets that are > potentially filled with columns that contain the aggregate data > repeated across many rows. By the way, I meant here by homing in: within the same query - using nested SELECTS. For example, the inner query performs the aggregate analysis, and returns whatever data we are interested in, and then the outer query performs another SELECT on the same data, resulting in the aggregate data being distributed across 'detailed results' from the outer query.
(csimam@gmail.com) writes:
[quoted text, click to view] > Which one is better? > 1. To perform aggregate queries on data, and use that data to home in > and perform more detailed analysis? This gives result sets that are > potentially filled with columns that contain the aggregate data > repeated across many rows. And you clarified: [quoted text, click to view] > By the way, I meant here by homing in: within the same query - using > nested SELECTS. For example, the inner query performs the aggregate > analysis, and returns whatever data we are interested in, and then the > outer query performs another SELECT on the same data, resulting in the > aggregate data being distributed across 'detailed results' from the > outer query. Your question is quite abstract, but to take a concrete example, it seems like you have sales data, and you want to aggregate this on per month, product, state, etc. You also want total sums per month, per product etc. The best is of course to get all at once in the same result set, since then you only need to scan the data once. in SQL 2005 you can achieve this with the proprietary extensions WITH CUBE and WITH ROLLUP to the GROUP BY clause. SQL 2008, currently in beta, implements GROUPING SETS from the ANSI standard, and are a lot more powerful. (They are also a lot more confusing to use.) -- 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
[quoted text, click to view]
On Sep 3, 1:05 am, Erland Sommarskog <esq...@sommarskog.se> wrote: > The best is of course to get all at once in the same result set, since > then you only need to scan the data once. in SQL 2005 you can achieve > this with the proprietary extensions WITH CUBE and WITH ROLLUP to > the GROUP BY clause. SQL 2008, currently in beta, implements GROUPING > SETS from the ANSI standard, and are a lot more powerful. (They are > also a lot more confusing to use.) Thank you Erland for your answer: I spent a bit of time reading about ROLLUP and CUBE, and found them to be almost exactly what I needed. But there is one obstacle that I am struggling with: The data I have, I want to generate totals and subtotals, say of sales: total sales per store, and subtotal of sales per store per day for example. The problem is that I ultimately only want the top 25 stores in total sales BUT with their subtotals as well... So I used ROLLUP to generate the totals and subtotals... and in the same query I generated a ranking of the data... and there I found my problem: the ranking does not distinguish between totals and subtotals. So the results look like: store1, realTotal, 1 store1, subTotal, 2 store2, realTotal, 3 store7, realTotal, 4 store10, subTotal, 5 Clearly, the rows with subtotals should NOT be ranked... but I am struggling with SQL to figure out how to get exactly what I want. Mind you, I've already got the problem solved but it involves a nested query that hits the table twice... and I just wanted to use the elegancy of ROLLUP to avoid hitting that table twice. Any ideas? thanks in advance.
[quoted text, click to view]
On Sep 4, 1:43 am, csi...@gmail.com wrote: > The data I have, I want to generate totals and subtotals, say of > sales: total sales per store, and subtotal of sales per store per day > for example. > > The problem is that I ultimately only want the top 25 stores in total > sales BUT with their subtotals as well... So I used ROLLUP to generate > the totals and subtotals... and in the same query I generated a > ranking of the data... and there I found my problem: the ranking does > not distinguish between totals and subtotals. So the results look > like: > > store1, realTotal, 1 > store1, subTotal, 2 > store2, realTotal, 3 > store7, realTotal, 4 > store10, subTotal, 5 > > Clearly, the rows with subtotals should NOT be ranked... but I am > struggling with SQL to figure out how to get exactly what I want. Well... I have a solution. But I'm not sure about it. It involves something I learned about only recently: a user-defined aggregate function that concatenates values from across rows. Using this aggregate function, stragg, I can do another pass through the results mentioned above while grouping by store, and produce: store1, 'realTotal subTotal', 1 store2, 'realTotal subTotal', 3 store7, 'realTotal subTotal', 4 store10, 'realTotal subTotal', 5 (it was a typo in the original example to list "store10, subTotal, 5" - the realTotal would have been greater than or equal). And another trick: use min( rank ) in the same aggregation, and order by it as well. Together that gives one a very compact result set that has everything ranked and grouped perfectly. ....but... a worry: is this REALLY better than doing a nested query? I wonder if the use of the user-defined aggregate function is very expensive... For the benefit of anyone wondering, here's the structure that works... in one pass in theory... but again, I am now wondering if it really is faster than the nested query equivalent that hits the database twice on the table 'stores' (seems to me that the middle select below is effectively hitting the table again, just in temporary form): select rownum as rank, id, days, totals from ( select id, stragg(day) as days, stragg(total) as totals, min( rank ) as innerrank from ( select id, day, sum(sales) as total, rank() over (order by sum(sales) desc) rank from stores group by id, rollup(day) ) group by id order by min(rank) ) where rownum <= 25
Erland,
Thanks for pointing the MVPs to this thread. I think the following solution is efficient and accesses the table only once. The query below uses the Northwind sample database data. It requires a permanent table of Years (year ranges, actually, with one range for each year and one "grand total" range) that can work generally for all queries like this, and it also uses aspects of the data model in Northwind, specifically that there is a Customers table. It can probably also be done with some sort of ROLLUP and PIVOT/UNPIVOT, as queries with cross joins and MAX's often can. Also, I only tested it on the SQL Server 2008 preview, because at the moment, no other server is handy. Here's the query, preceded by the created Years table (here with inserts only sufficient for Northwind. You will have to add more.) Steve Kass Drew University http://www.stevekass.com -- Create a permanent table like this that will work for -- the entire range of time you will ever need CREATE TABLE Years( y0 datetime primary key, y1 datetime, tag varchar(20), ord int ); insert into Years values ('1996','1997','1996',1996); insert into Years values ('1997','1998','1997',1997); insert into Years values ('1998','1999','1998',1998); insert into Years values ('1900','2100','Total',0); go -- The query: with T(CustomerID, Tag, Ord, Total, drk) as ( select C.CustomerID, max(case when O.OrderDate >= y0 and O.OrderDate < y1 then tag end ) as Tag, max(case when O.OrderDate >= y0 and O.OrderDate < y1 then ord end ) as Ord, sum( case when O.OrderDate >= y0 and O.OrderDate < y1 then OD.Quantity * OD.UnitPrice end ) as Total, dense_rank() over (order by sum(OD.Quantity * OD.UnitPrice) desc) as drk from Orders O join Customers C on C.CustomerID = O.CustomerID join [Order Details] OD on O.OrderID = OD.OrderID cross join Years group by C.CustomerID, tag ) select CustomerID, Tag, Total from T where drk <= 25 order by drk, Ord ; go -- DROP TABLE Years; [quoted text, click to view] Erland Sommarskog wrote:
> (csimam@gmail.com) writes: > >>>The problem is that I ultimately only want the top 25 stores in total >>>sales BUT with their subtotals as well... So I used ROLLUP to generate >>>the totals and subtotals... and in the same query I generated a >>>ranking of the data... and there I found my problem: the ranking does >>>not distinguish between totals and subtotals. So the results look >>>like: >>> >>>store1, realTotal, 1 >>>store1, subTotal, 2 >>>store2, realTotal, 3 >>>store7, realTotal, 4 >>>store10, subTotal, 5 >>> >>>Clearly, the rows with subtotals should NOT be ranked... but I am >>>struggling with SQL to figure out how to get exactly what I want. > > > 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. > > >>Well... I have a solution. But I'm not sure about it. It involves >>something I learned about only recently: a user-defined aggregate >>function that concatenates values from across rows. Using this >>aggregate function, stragg, I can do another pass through the results >>mentioned above while grouping by store, and produce: >> >>store1, 'realTotal subTotal', 1 >>store2, 'realTotal subTotal', 3 >>store7, 'realTotal subTotal', 4 >>store10, 'realTotal subTotal', 5 > > > One problem is that a UDA is limited to 8000 characters, so if there > are many subtotals, you query will bomb. > > >>...but... a worry: is this REALLY better than doing a nested query? I >>wonder if the use of the user-defined aggregate function is very >>expensive... > > > The way to find out is to run both queries a couple of times on an > idle server. Just don't forget about the effects of caching. Either > first run the queries once, so that you know that table is entirely > in cache. If it's too big for that, run DBCC DROPCLEANBUFFERS between > each run, so that both queries run under the same condition. >
(csimam@gmail.com) writes:
[quoted text, click to view] >> The problem is that I ultimately only want the top 25 stores in total >> sales BUT with their subtotals as well... So I used ROLLUP to generate >> the totals and subtotals... and in the same query I generated a >> ranking of the data... and there I found my problem: the ranking does >> not distinguish between totals and subtotals. So the results look >> like: >> >> store1, realTotal, 1 >> store1, subTotal, 2 >> store2, realTotal, 3 >> store7, realTotal, 4 >> store10, subTotal, 5 >> >> Clearly, the rows with subtotals should NOT be ranked... but I am >> struggling with SQL to figure out how to get exactly what I want. 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. [quoted text, click to view] > Well... I have a solution. But I'm not sure about it. It involves > something I learned about only recently: a user-defined aggregate > function that concatenates values from across rows. Using this > aggregate function, stragg, I can do another pass through the results > mentioned above while grouping by store, and produce: > > store1, 'realTotal subTotal', 1 > store2, 'realTotal subTotal', 3 > store7, 'realTotal subTotal', 4 > store10, 'realTotal subTotal', 5 One problem is that a UDA is limited to 8000 characters, so if there are many subtotals, you query will bomb. [quoted text, click to view] > ...but... a worry: is this REALLY better than doing a nested query? I > wonder if the use of the user-defined aggregate function is very > expensive... The way to find out is to run both queries a couple of times on an idle server. Just don't forget about the effects of caching. Either first run the queries once, so that you know that table is entirely in cache. If it's too big for that, run DBCC DROPCLEANBUFFERS between each run, so that both queries run under the same condition. -- 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
Erland Sommarskog (esquel@sommarskog.se) writes:
[quoted text, click to view] > 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
Erland Sommarskog (esquel@sommarskog.se) writes:
[quoted text, click to view] > 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. [quoted text, click to view] > 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
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |