Faster way to do this?

Question

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 ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- 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 = 2006

set @year2 = 2007

 

SELECT month,[2006] as y2006,[2007] as y2007

FROM

(

SELECT @year1 AS year, 1 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 1)) as tmpy1_1

UNION

SELECT @year1 AS year, 2 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 2)) as tmpy1_2

UNION

SELECT @year1 AS year, 3 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 3)) as tmpy1_3

UNION

SELECT @year1 AS year, 4 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 4)) as tmpy1_4

UNION

SELECT @year1 AS year, 5 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 5)) as tmpy1_5

UNION

SELECT @year1 AS year, 6 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 6)) as tmpy1_6

UNION

SELECT @year1 AS year, 7 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 7)) as tmpy1_7

UNION

SELECT @year1 AS year, 8 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 8)) as tmpy1_8

UNION

SELECT @year1 AS year, 9 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 9)) as tmpy1_9

UNION

SELECT @year1 AS year, 10 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 10)) as tmpy1_10

UNION

SELECT @year1 AS year, 11 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 11)) as tmpy1_11

UNION

SELECT @year1 AS year, 12 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 12)) as tmpy1_12

UNION

SELECT @year2 AS year, 1 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 1)) as tmpy1_1

UNION

SELECT @year2 AS year, 2 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 2)) as tmpy2_2

UNION

SELECT @year2 AS year, 3 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 3)) as tmpy2_3

UNION

SELECT @year2 AS year, 4 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 4)) as tmpy2_4

UNION

SELECT @year2 AS year, 5 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 5)) as tmpy2_5

UNION

SELECT @year2 AS year, 6 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 6)) as tmpy2_6

UNION

SELECT @year2 AS year, 7 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 7)) as tmpy2_7

UNION

SELECT @year2 AS year, 8 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 8)) as tmpy2_8

UNION

SELECT @year2 AS year, 9 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 9)) as tmpy2_9

UNION

SELECT @year2 AS year, 10 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 10)) as tmpy2_10

UNION

SELECT @year2 AS year, 11 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 11)) as tmpy2_11

UNION

SELECT @year2 AS year, 12 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 12)) as tmpy2_12

) piv

PIVOT

(

SUM(cnt)

FOR year IN

([2006],[2007])

) as child

END