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
bmcclearyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
i think your problem is more likely to be the 95 scans of #schedule you are causing for each employee row...

use this format an have just the 1 pass of the data

select empid,
   sum(case when 1 between stp and etp then 1 else null end) as [1]
  , sum(case when 2 between stp and etp then 1 else null end) as [2]
  , sum(case when 3 between stp and etp then 1 else null end) as [3]
  , sum(case when 4 between stp and etp then 1 else null end) as [4]
  , sum(case when 5 between stp and etp then 1 else null end) as [5]
  , sum(case when 6 between stp and etp then 1 else null end) as [6]
from #schedule
group by empid




you may also wish to try this out which can generate pivot table SQL for you...

http://www.microsoft.com/sql/ce/techinfo/20bol.asp

EXEC sp_TRANSFORM 'SUM','PM_EmpAssignedID','vw_Loc_Pos_Dflt_Rem2','PM_PositionsID','Position'

IF OBJECT_ID('sp_Transform', 'P') IS NOT NULL
     DROP PROCEDURE sp_Transform
GO
CREATE PROCEDURE sp_TRANSFORM
/*
   Purpose:     Creates a Pivot(tm) table for the specified table,
                view or select statement
   Author:      svenh@itrain.de
   Version:     1.1
   History:     march 2000 version 1.0
                july 2002  version 1.1

   Input parameters:
       @Aggregate_Function (optional)
            the aggregate function to use for the pivot
                default function is SUM
        @Aggregate_Column
                name of column for aggregate
        @TableOrView_Name
                name of table or view to use
                if name contains spaces or other special
                characters [] should be used
                Can also be a valid SELECT statement
        @Select_Column
                Column for first column in result table
                for this column row values are displayed
        @Pivot_Column
                Column that is transformed into columns
                for this column column values are displayed
     @DEBUG
          Set this flag to 1 to get debug-information
   
      Example usage:
        Table given   aTable
        content:      Product    Salesman    Sales
                      P1         Sa          12
                      P2         Sb          10
                      P2         Sb          3
                      P3         Sa          12
                      P1         Sc          8
                      P3         Sa          1
                      P2         Sa          NULL
     CALL
     EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
or      EXEC sp_Transform @Aggregate_Column='Sales', @TableOrViewName='aTable',
                            @Select_Column='Product', @Pivot_Column='Salesman'

Result:
        Product| Sa       | Sb       | Sc      | Total      
        -------+----------+----------+---------+---------
        P1     | 12,00    |  0,00    |  8,00   |  20,00
        P2     |  0,00    | 13,00    |  0,00   |  13,00
        P3     | 13,00    |  0,00    |  0,00   |  13,00
        -------+----------+----------+---------+---------
        Total  | 25,00    | 13,00    |  8,00   |  46,00


*/
     @Aggregate_Function nvarchar(30) = 'SUM',
     @Aggregate_Column   nvarchar(255),
     @TableOrView_Name   nvarchar(255),
     @Select_Column         nvarchar(255),
     @Pivot_Column       nvarchar(255),
     @DEBUG              bit = 0
AS
SET NOCOUNT ON
DECLARE @TransformPart   nvarchar(4000)
DECLARE @SQLColRetrieval nvarchar(4000)
DECLARE @SQLSelectIntro  nvarchar(4000)
DECLARE @SQLSelectFinal  nvarchar(4000)

IF @Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')  
     BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1, @Aggregate_Function) END
ELSE
BEGIN
     SELECT @SQLSelectIntro = 'SELECT CASE WHEN (GROUPING('  +
                         QUOTENAME(@Select_Column)       +
                    ') = 1) THEN ''Total'' ELSE '   +
                    'CAST( + '                      +
                                QUOTENAME(@Select_Column)       +
                    ' AS NVARCHAR(255)) END As '    +
                    QUOTENAME(@Select_Column)       +
                    ', '
     IF @DEBUG = 1 PRINT @sqlselectintro
     SET @SQLColRetrieval =
     N'SELECT @TransformPart = CASE WHEN @TransformPart IS NULL THEN ' +
               N'''' + @Aggregate_Function + N'(CASE CAST(' +
               QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
               N' AS VARCHAR(255)) WHEN '''''' + CAST('  +
               QUOTENAME(@Pivot_Column) +
               N' AS NVarchar(255)) + '''''' THEN ' + @Aggregate_Column +
               N' ELSE 0 END) AS '' + QUOTENAME(' +
               QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
               N') ELSE  @TransformPart + '', ' + @Aggregate_Function +
               N' (CASE CAST(' + QUOTENAME(@Pivot_Column) +
               N' AS nVARCHAR(255)) WHEN '''''' + CAST(' +
               QUOTENAME(CAST(@Pivot_Column As VarChar(255))) +
               N' AS nVARCHAR(255)) + '''''' THEN ' +
               @Aggregate_Column +
               N' ELSE 0 END) AS '' + QUOTENAME(' +
               QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
               N') END FROM (SELECT DISTINCT ' +
               QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
               N' FROM ' + @TableOrView_Name + ') SelInner'
     IF @DEBUG = 1 PRINT @SQLColRetrieval    
     EXEC sp_executesql @SQLColRetrieval,
                           N'@TransformPart nvarchar(4000) OUTPUT',
                           @TransformPart OUTPUT
     IF @DEBUG = 1 PRINT @TransformPart
     SET @SQLSelectFinal =
                           N', ' + @Aggregate_Function + N'(' +
                           CAST(@Aggregate_Column As Varchar(255)) +
                           N') As Total FROM ' + @TableOrView_Name + N' GROUP BY ' +
                           @Select_Column + N' WITH CUBE'
     IF @DEBUG = 1 PRINT @SQLSelectFinal
     EXEC (@SQLSelectIntro + @TransformPart + @SQLSelectFinal)
END
GO


/*
To test the preceding stored procedure uncomment the
following section

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_NAME = 'aTable')
   DROP TABLE aTable  
--GO
 
CREATE TABLE aTable(Product varchar(2), Salesman varchar(2), Sales int)
INSERT INTO aTable VALUES('P1', 'Sa', 12)
INSERT INTO aTable VALUES('P2', 'Sb', 10)
INSERT INTO aTable VALUES('P2', 'Sb', 3)
INSERT INTO aTable VALUES('P3', 'Sa', 12)
INSERT INTO aTable VALUES('P1', 'Sc', 8)
INSERT INTO aTable VALUES('P3', 'Sa', 1)
INSERT INTO aTable VALUES('P2', 'Sa', NULL)

EXEC sp_Transform @Aggregate_Column='Sales', @TableOrView_Name='aTable',
                            @Select_Column='Product', @Pivot_Column='Salesman'

*/
-- Example from Northwind Table
-- USE NORTHWIND
-- GO
-- EXEC sp_transform 'sum', 'unitprice', 'products', 'productname', 'CategoryID'
-- GO


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bmcclearyAuthor Commented:
Sorry for the delay.  Your first option worked PERFECTLY.  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.