Sql-server – Get Sale Info By Employee

sql serversql-server-2008-r2t-sql

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

Best Answer

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.

Related Question