0

I need a way to get detailed sale information for each employee in the StoreInfo table. Each employeename in that table has it's own table with data structure mirroring the table information below. I thought maybe a cursor would be the best bet, but it is taking roughly 25 minutes to aggregate all of this data. Below is my sample DDL - how can I aggregate this data w/o it taking this extend period of time?

Create Table stores
(
  storeID int NOT NULL IDENTITY (1,1) PRIMARY KEY
  ,storename varchar(500)
)

Insert Into stores Values ('WS'), ('RBG'), ('CY'), ('MF')

Create Table storeinfo
(
  storeinfoID int NOT NULL IDENTITY (1,1) PRIMARY KEY
  ,storeID int FOREIGN KEY REFERENCES stores(storeID)
  ,employeename varchar(500)
  ,employeecity varchar(100)
  ,employeestate varchar(100)
)

INSERT INTO storeinfo(storeID, employeename, employeecity, employeestate) VALUES
('1', 'BobGodfrey', 'Mine', 'WV'), ('1', 'RoyBlue', 'Merk', 'SC'), ('1', 'GreenState', 'Merk', 'SC'),
('2', 'PinkSox', 'Yellow', 'CA'), ('2', 'BlueHoodie', 'Yellow', 'CA'), ('3', 'PurpleCow', 'Ridata', 'NY'),
('3', 'StinkySoles', 'Ridata', 'NY'), ('4', 'MetalJacket', 'Bells', 'MN'),   ('4', 'DimensionalData', 'Bells', 'NY')

Create Table BobGodfrey
(
  bobgodfreyID int NOT NULL IDENTITY (1,1) PRIMARY KEY
  ,storeID int FOREIGN KEY REFERENCES stores(storeID)
  ,itemsold varchar(500)
  ,datesold date
)

Insert Into BobGodfrey (storeID, itemsold, datesold) VALUES
('1', 'pants', '02/01/2015'), ('1', 'pants', '02/01/2015'), ('1', 'pants', '02/01/2015'),
('1', 'shirt', '02/01/2015'), ('1', 'pants', '02/01/2015'), ('1', 'pants', '02/01/2015')

Declare @empname varchar(500), @inlinesql varchar(max)

Create Table holdingtable
(
  employeename varchar(500)
  ,saledate date
  ,itemsold varchar(500)
  ,totalsales int
)

DECLARE db_cursor CURSOR FOR  
SELECT employeename 
FROM storeinfo  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @empname   

WHILE @@FETCH_STATUS = 0   
BEGIN   

   set @inlinesql = 'Select '+@empname+' As employeename, saledate, itemsold, COUNT(itemsold) As [totalsales] '
                    +'INTO holdingtable '
                    +'FROM '+@empname+' '
   Exec(@inlinesql)    

   FETCH NEXT FROM db_cursor INTO @empname   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Select * from holdingtable
  • 3
    Why on earth would you have a table per person? Just have a single table with the employee as a column. – Max Vernon Feb 25 '16 at 5:18
  • @MaxVernon - This was the cadence that was followed in the beginning. It is a work in progress to update everything to have one master table housing all the data, but is not complete yet... – FunFly WhiteGuy Feb 25 '16 at 13:12
2
Declare @SelectSql nvarchar(max), @InsertSql nvarchar(max);

SELECT @SelectSql = COALESCE(@SelectSql + ' UNION ALL ', '') +
                'Select '''+employeename+''' As employeename, datesold, itemsold, COUNT(itemsold)  As [totalsales] '
                +'FROM '+employeename+' '
                FROM storeinfo;
-- print @SelectSql;
-- Exec(@inlinesql);

SET @InsertSql = 'Insert into holdingtable ' + @SelectSql;

print @InsertSql;
exec (@InsertSql);

Not sure wheather it will help with performance, but surely is more elegant than cursor.

Edit (as requested by Andriy):

If it takes so long, consider aggregating data more often, I mean divide the work to smaller chunks. For example you can every hour take only the data that was added during this time, aggregate, add to the table. This would require more work. Maybe you will need include some table for pre-aggregations -> aggregate every hour to HourlyHoldingTable, then every nigth aggregate this much smaller table to the DailyHodlingTable. Or only add a comumn with a number of rows aggregated so far, to enable correct update. Many solutions possible here. The general idea is to split the big task to more smaller tasks.

Or you could have an indexed view with aggregation for each table.

The problem is, we don't have enough information - the query you pasted is not complete (missing group by), so it is hard to tell wether problem is in grouping huge number of rows, or in inserting loads of rows.

  • The way you are composing your dynamic query, I'm sure GROUP BY datesold, itemsold won't be amiss in it. – Andriy M Feb 25 '16 at 10:17
  • @AndriyM, you're right, the select query itself is not working, it is a copy of a query from the question, I'd rather not guess what/how really needs to be aggregated, I'm sure the query author will be able to replace his own simplified query with the actual query – Limonka Feb 25 '16 at 10:23
  • Oh I see, missed that in OP's script, sorry. I've got two more issues with your answer, though. First, what do you mean by If it takes so long, consider aggregating data more often? How can aggregating data more often make the process faster? And secondly, you cannot index a view that has a UNION [ALL]. – Andriy M Feb 25 '16 at 10:38
  • Good point with indexed view, missed that. I mean that he can every hour take only the data that was added during this time, aggregate, add to the table. This would require more work. Maybe include some table for preaggregations -> aggregate every hour to HourlyHoldingTable, then every nigth aggregate this much smaller table to the DailyHodlingTable. Many other workouronds possible. The general idea is to split the big task to smaller tasks. – Limonka Feb 25 '16 at 10:49
  • Well, if you meant that, please consider mentioning that in your answer. Otherwise it sounds like you are suggesting the OP just carry on doing what they do but more often. For all I know they could be aggregating all the data in all employee tables every time – surely, doing that more often won't help performance. – Andriy M Feb 25 '16 at 11:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.