USE tempdb;
CREATE TABLE Managers(
ManagerName VARCHAR(30) PRIMARY KEY CLUSTERED
);
go
CREATE TABLE Approvals(
QuestionID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, --This is the easy PK:)
ManagerName VARCHAR(30) REFERENCES Managers(ManagerName),
Approval CHAR(1) CHECK (Approval IN ('Y','N'))
);
go
CREATE CLUSTERED INDEX IX_M_A ON Approvals( ManagerName , Approval );
set nocount on;
insert into managers values ('Bob');
insert into managers values ('Jim');
insert into managers values ('Sam');
insert into managers values ('Tom');
insert into managers values ('Bob2');
insert into managers values ('Jim2');
insert into managers values ('Sam2');
insert into managers values ('Tom2');
insert into managers values ('Bob3');
insert into managers values ('Jim3');
insert into managers values ('Sam3');
insert into managers values ('Tom3');
SELECT * FROM Managers;
/*
ManagerName
------------------------------
Bob
Bob2
Bob3
Jim
Jim2
Jim3
Sam
Sam2
Sam3
Tom
Tom2
Tom3
*/
-- Insert sample data. Code provided by: "Stefan Gustafsson"
begin tran;
declare @i int;
set @i=3000;
while @i>0 begin
insert into approvals (ManagerName, Approval) VALUES ('Bob', 'Y');
insert into approvals (ManagerName, Approval) VALUES ('Tom', null);
insert into approvals (ManagerName, Approval) VALUES ('Tom', 'N');
insert into approvals (ManagerName, Approval) VALUES ('Jim',null);
insert into approvals (ManagerName, Approval) VALUES ('Jim', 'Y');
insert into approvals (ManagerName, Approval) VALUES ('Jim', 'Y');
set @i=@i-1;
end;
commit tran;
SELECT * FROM Approvals;
-- Sample output of data shown below:
/*
QuestionID ManagerName Approval
----------- ------------------------------ --------
1 Bob Y
2 Tom NULL
3 Tom N
4 Jim NULL
5 Jim Y
6 Jim Y
7 Bob Y
8 Tom NULL
9 Tom N
10 Jim NULL
11 Jim Y
12 Jim Y
13 Bob Y
14 Tom NULL
15 Tom N
16 Jim NULL
17 Jim Y
18 Jim Y
*/
-- Objective:
/*
To count the approval values for each mananger.
Provided are 3 different ways to solve the problem.
Add lot of data using the loop above & you will see
that the peformance varies & the 3rd query will be effective
on large data sets. Try the test yourself & analyze each SELECT.
Here is some of the parameters that I measured using SQL Profiler:
CPU Reads Writes Duration
--------------------------------
190 353 0 630 (Run #1 , Query #1)
371 216 0 670 (Run #1 , Query #1)
170 229 0 533 (Run #1 , Query #1)
190 353 0 693 (Run #2 , Query #2)
361 216 0 670 (Run #2 , Query #2)
180 229 0 640 (Run #2 , Query #2)
211 353 0 670 (Run #1 , Query #3)
320 216 0 680 (Run #1 , Query #3)
201 229 0 430 (Run #1 , Query #3)
*/
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
-- A straight-forward sub-query solution provided by
-- "Alan Mitchell" & "Ivan Arjentinski".
SELECT Managers.ManagerName,
(SELECT Count(*)
FROM Approvals
WHERE Approval = 'Y' AND
ManagerName=Managers.ManagerName
) AS 'Yes',
(SELECT Count(*)
FROM Approvals
WHERE Approval = 'N' AND
ManagerName=Managers.ManagerName
) AS 'No',
(SELECT Count(*)
FROM Approvals
WHERE Approval IS NULL AND
ManagerName=Managers.ManagerName
) AS 'Null Values'
FROM Managers
ORDER BY ManagerName;
GO
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
-- The obvious Case solution using a LEFT JOIN provided by: "Stefan Gustafsson"
SELECT
a.ManagerName,
SUM(CASE WHEN b.Approval='Y' THEN 1 ELSE 0 END) as Y,
SUM(CASE WHEN b.Approval='N' THEN 1 ELSE 0 END) as N,
SUM(CASE WHEN b.Approval is null and b.ManagerName is not null THEN 1 ELSE 0
END) as nul
FROM Managers a
LEFT JOIN Approvals b
ON a.ManagerName = b.ManagerName
GROUP BY a.ManagerName
ORDER BY a.ManagerName;
GO
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
-- This is my query using the clustered index changes that I suggested
-- and a UNION ALL query. This is a different approach to the entire
-- problem that reduces the 1st SELECT to a clustered index scan & the
-- second to a powerful NOT EXISTS query.
SELECT
a.ManagerName,
SUM(CASE WHEN a.Approval='Y' THEN 1 ELSE 0 END) as Y,
SUM(CASE WHEN a.Approval='N' THEN 1 ELSE 0 END) as N,
SUM(CASE WHEN a.Approval is null THEN 1 ELSE 0 END) as nul
FROM Approvals As a
GROUP BY a.ManagerName
UNION ALL
SELECT m.ManagerName , 0 , 0 , 0
FROM Managers AS m
WHERE NOT EXISTS( SELECT * FROM Approvals AS a
WHERE a.ManagerName = m.ManagerName )
ORDER BY ManagerName;
GO
DROP TABLE Approvals;
DROP TABLE Managers;
This page was last updated on May 01, 2006 04:28 PM.