Aggregation and sorting on very large tables.

Last Post 23 May 2008 10:20 AM by SQLUSA. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sunliners81
New Member
New Member

--
19 May 2008 12:20 PM
I'm having a problem with running out of tempdb space when attemping an aggregate query with group by. I'm attempting to sum two columns. If I just select count(*) on the from statement, 721,204,305 rows are returned. What could be an alternitive here?

---------- snip -------------------

SELECT
A.NDC,
DATEPART(MM, A.[Invoice Date]) AS month,
A.Manufacturer,
A.calculated_size_number,
SUM(A.[Quantity Shipped]) AS tot_quant,
SUM(A.[Extended Invoice Price]) AS tot_invoice_sales,
B.[1997_EAC] AS exhibit_eac,
M.retail_flag,
M.wi_flag
FROM Wholesaler.SALES_FINAL_View AS A INNER JOIN
Wholesaler.Exhibit AS B ON A.NDC = B.NDC LEFT OUTER JOIN
Wholesaler.Mapping AS M ON A.State = M.State AND A.[Accounting Class Code] = M.[Accounting Class Code]
WHERE (B.[1997_EAC] IS NOT NULL)
AND (A.[Invoice Date] BETWEEN '1997-01-01 00:00:00.000' AND '1997-12-31 00:00:00.000')
AND (A.[Transaction Description] = 'REGULAR INVENTORY BILLING')
GROUP BY
A.NDC,
DATEPART(MM, A.[Invoice Date]),
A.Manufacturer,
A.calculated_size_number,
B.[1997_EAC],
M.retail_flag,
M.wi_flag;



Thanks, Clay
SwePeso
New Member
New Member

--
21 May 2008 12:31 PM
You have 721 million records and you do a GROUP BY over 7 columns of which one is calculated?

SELECT A.NDC,
DATEPART(MM, A.[Invoice Date]) AS month,
A.Manufacturer,
A.calculated_size_number,
SUM(A.[Quantity Shipped]) AS tot_quant,
SUM(A.[Extended Invoice Price]) AS tot_invoice_sales,
B.[1997_EAC] AS exhibit_eac,
M.retail_flag,
M.wi_flag
FROM Wholesaler.SALES_FINAL_View AS A
INNER JOIN Wholesaler.Exhibit AS B ON B.NDC = A.NDC
LEFT JOIN Wholesaler.Mapping AS M ON M.State = A.State
AND M.[Accounting Class Code] = A.[Accounting Class Code]
WHERE B.[1997_EAC IS NOT NULL
AND A.[Invoice Date] >= '19970101'
AND A.[Invoice Date] < '19980101'
AND A.[Transaction Description] = 'REGULAR INVENTORY BILLING'
GROUP BY A.NDC,
DATEPART(MM, A.[Invoice Date]),
A.Manufacturer,
A.calculated_size_number,
B.[1997_EAC],
M.retail_flag,
M.wi_flag
SQLUSA
New Member
New Member

--
23 May 2008 10:20 AM
quote:

Originally posted by: sunliners81
I'm having a problem with running out of tempdb space when attemping an aggregate query with group by. I'm attempting to sum two columns. If I just select count(*) on the from statement, 721,204,305 rows are returned. What could be an alternitive here?




Building an OLAP cube maybe your solution. OLAP cubes are the champions of aggregation. In fact OLAP cubes are nothing but aggregations.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 OLAP Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
You are not authorized to post a reply.

Acceptable Use Policy