I want to know the # of users on our web site for each month in a given year. I'm looking for a faster way to do this--perhaps one that can leverage an index instead of reading the entire table! (My avg disk queue right now is above 7 and the query takes about 90 seconds).
Here's my current SP. Basically I'm calculating each month/year and using UNION to join them together, then pivot to rotate.
USE
[TNS]GO
/****** Object: StoredProcedure [dbo].[Unique_Login_IPs] Script Date: 05/07/2007 12:38:52 ******/
SET
ANSI_NULLS ONGO
SET
QUOTED_IDENTIFIER ONGO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER
PROCEDURE [dbo].[Unique_Login_IPs](
@year1
int,@year2
int)
AS
BEGIN
SET NOCOUNT OFF;
-- Define the years for testing purposes
set
@year1 = 2006set
@year2 = 2007
SELECT
month,[2006] as y2006,[2007] as y2007FROM
(
SELECT
@year1 AS year, 1 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 1)) as tmpy1_1UNION
SELECT
@year1 AS year, 2 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 2)) as tmpy1_2UNION
SELECT
@year1 AS year, 3 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 3)) as tmpy1_3UNION
SELECT
@year1 AS year, 4 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 4)) as tmpy1_4UNION
SELECT
@year1 AS year, 5 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 5)) as tmpy1_5UNION
SELECT
@year1 AS year, 6 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 6)) as tmpy1_6UNION
SELECT
@year1 AS year, 7 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 7)) as tmpy1_7UNION
SELECT
@year1 AS year, 8 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 8)) as tmpy1_8UNION
SELECT
@year1 AS year, 9 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 9)) as tmpy1_9UNION
SELECT
@year1 AS year, 10 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 10)) as tmpy1_10UNION
SELECT
@year1 AS year, 11 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 11)) as tmpy1_11UNION
SELECT
@year1 AS year, 12 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 12)) as tmpy1_12UNION
SELECT
@year2 AS year, 1 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 1)) as tmpy1_1UNION
SELECT
@year2 AS year, 2 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 2)) as tmpy2_2UNION
SELECT
@year2 AS year, 3 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 3)) as tmpy2_3UNION
SELECT
@year2 AS year, 4 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 4)) as tmpy2_4UNION
SELECT
@year2 AS year, 5 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 5)) as tmpy2_5UNION
SELECT
@year2 AS year, 6 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 6)) as tmpy2_6UNION
SELECT
@year2 AS year, 7 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 7)) as tmpy2_7UNION
SELECT
@year2 AS year, 8 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 8)) as tmpy2_8UNION
SELECT
@year2 AS year, 9 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 9)) as tmpy2_9UNION
SELECT
@year2 AS year, 10 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 10)) as tmpy2_10UNION
SELECT
@year2 AS year, 11 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 11)) as tmpy2_11UNION
SELECT
@year2 AS year, 12 AS month, COUNT(*) AS cntFROM
(SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 12)) as tmpy2_12)
pivPIVOT
(
SUM
(cnt)FOR
year IN(
[2006],[2007]))
as childEND