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

x
Solved

# Improving Pivot Table Retreival Performance

Posted on 2004-11-13
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
Question by:bmccleary
2 Comments

LVL 50

Accepted Solution

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

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

## Featured Post

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
Course of the Month19 days, 16 hours left to enroll

#### 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.