[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Improving Pivot Table Retreival Performance

Posted on 2004-11-13
2
Medium Priority
?
2,521 Views
Last Modified: 2008-01-09
(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
0
Comment
Question by:bmccleary
2 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 12575802
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
 

Author Comment

by:bmccleary
ID: 12597520
Sorry for the delay.  Your first option worked PERFECTLY.  Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question