Alternative Needed for the Queries

Hi

I’m facing this very unusual problem. Let me first give a background –

This is a system consisting of Distribuors, Dealers and Brands.
I have few tables as mentioned below (these are just prototypes of the actual table):

• ‘REL’ table: This stores the Distributors and Dealers relationship. E.g. say
DISTRIBUTOR DEALER BRAND
Dist1 Dlr1 Br1
Dist1 Dlr2 Br1
Dist2 Dlr1 Br1
Dist2 Dlr2 Br2

So it maintains as to who is the distributor of a particular brand who is the Distributor for a particular Dealer.

• ‘COMPANYTREE’ table: This table holds the Distributor Hierarchy. i.e. if Dist 2 is under Dist 1, then this table will have a record for Dist 2 where the hierarchy-string for Dist 2 will contain the id of Dist 1 and Dist2 both. Similarly, if DIST 1 is the topmost distributor it will just have its id in that string. So a third distributor Dist3 under Dist2 will have record as shown below:

DISTRIBUTOR HIERARCHY STRING
Dist1 111-1111-DIST1
Dist2 111-111-DIST1-DIST2
Dist3 111-111-DIST1-DIST2-DIST3

• ‘IMPRESSIONS’ table: This table contains a number (Impressions) for each dealer-brand combination in the system for different times across a day, for all the days. Needless to say that this table is VERY LARGE and is growing at giant’s pace. This has fields like:
DealerId, Brand, Impression Count, DateTime



Now here is my problem:
I need to get the sum of impressions for all the dealers who are under a distributor’s realm and put it into a new table. That is to say, for any Distributor, for each day and brand, I need to sum up the impressions of all the dealers that are directly under him or are under the distributors in his hierarchy. So for a sample case put above- For Dist1, I should have the summed up Impressions of the dealers Dlr1-Br1, Dlr2-Br1 and Dlr2-Br2. It should be noticed that since Dlr1-Br1 combo appears under both Dist1 and Dist2, the impressions for this should not be added twice while summing up data for Dist1.

I tried doing it using a stored procedure that uses normal joins. But the bulk of data is so huge it seems that the stored-procedure throws an error long before it is fully executed. The error is as follows:
"Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."

So can anyone please suggest any other approach for getting this issue resolved wherein I distinctly sum up the data for the distributor’s hierarchy?


Thanks in Advance
Dex