bmccleary
asked on
Improving Pivot Table Retreival Performance
(Note: Complete Example Code Provided Below)
I have a SQL statement that creates a cross tab or pivot table for a group of employees, that breaks down the day into 15 minute increments, so there are 96 'time periods' available for any given day and displays whether or not each employee is working during that time period. The problem is, the SQL command is somewhat slow and very processor intesive. I believe this is due to the number of columns that must be generated (96) and the use of COUNT(*) as opposed to IF EXISTS. So my question is this, given the pivot table SQL below, how can I improve the performance (and shear length) of the SQL command? Any help is appreciated!
As an example, say I have three employees, working from 7-4, 8-5 and 9-6 respectivly (STP is starting time period, ETP is ending). Here's the script to create that data and return the pivot table (or at least just a few periods from the table):
-- Create example table
CREATE TABLE #Schedule(EmpID int, STP int, ETP int)
INSERT INTO #Schedule VALUES (1, 29, 65)
INSERT INTO #Schedule VALUES (2, 33, 69)
INSERT INTO #Schedule VALUES (3, 37, 73)
-- Create Cross-Tab (pivot table) results for each time period 1 through 96
SELECT
EmpID,
(SELECT COUNT(*) FROM #Schedule WHERE EmpID = S.EmpID AND 1 BETWEEN S.STP AND S.ETP) as [1],
(SELECT COUNT(*) FROM #Schedule WHERE EmpID = S.EmpID AND 2 BETWEEN S.STP AND S.ETP) as [2],
'' as [3...28], -- Same thing for time periods 3 through 28,
(SELECT COUNT(*) FROM #Schedule WHERE EmpID = S.EmpID AND 28 BETWEEN S.STP AND S.ETP) as [28],
(SELECT COUNT(*) FROM #Schedule WHERE EmpID = S.EmpID AND 29 BETWEEN S.STP AND S.ETP) as [29],
'' as [30...95], -- And on and on for time periods 30 through 95
(SELECT COUNT(*) FROM #Schedule WHERE EmpID = S.EmpID AND 96 BETWEEN S.STP AND S.ETP) as [96]
FROM #Schedule S
-- Drop example table
DROP TABLE #Schedule
I have a SQL statement that creates a cross tab or pivot table for a group of employees, that breaks down the day into 15 minute increments, so there are 96 'time periods' available for any given day and displays whether or not each employee is working during that time period. The problem is, the SQL command is somewhat slow and very processor intesive. I believe this is due to the number of columns that must be generated (96) and the use of COUNT(*) as opposed to IF EXISTS. So my question is this, given the pivot table SQL below, how can I improve the performance (and shear length) of the SQL command? Any help is appreciated!
As an example, say I have three employees, working from 7-4, 8-5 and 9-6 respectivly (STP is starting time period, ETP is ending). Here's the script to create that data and return the pivot table (or at least just a few periods from the table):
-- Create example table
CREATE TABLE #Schedule(EmpID int, STP int, ETP int)
INSERT INTO #Schedule VALUES (1, 29, 65)
INSERT INTO #Schedule VALUES (2, 33, 69)
INSERT INTO #Schedule VALUES (3, 37, 73)
-- Create Cross-Tab (pivot table) results for each time period 1 through 96
SELECT
EmpID,
(SELECT COUNT(*) FROM #Schedule WHERE EmpID = S.EmpID AND 1 BETWEEN S.STP AND S.ETP) as [1],
(SELECT COUNT(*) FROM #Schedule WHERE EmpID = S.EmpID AND 2 BETWEEN S.STP AND S.ETP) as [2],
'' as [3...28], -- Same thing for time periods 3 through 28,
(SELECT COUNT(*) FROM #Schedule WHERE EmpID = S.EmpID AND 28 BETWEEN S.STP AND S.ETP) as [28],
(SELECT COUNT(*) FROM #Schedule WHERE EmpID = S.EmpID AND 29 BETWEEN S.STP AND S.ETP) as [29],
'' as [30...95], -- And on and on for time periods 30 through 95
(SELECT COUNT(*) FROM #Schedule WHERE EmpID = S.EmpID AND 96 BETWEEN S.STP AND S.ETP) as [96]
FROM #Schedule S
-- Drop example table
DROP TABLE #Schedule
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER