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

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

(

@year1 int,

@year2 int

)

AS

BEGIN

SET NOCOUNT OFF;

-- Define the years for testing purposes

set @year1 = 2006

set @year2 = 2007

SELECT month, as y2006, as y2007

FROM

(

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

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

(,)

) as child

END

Monday, May 7, 2007 6:35 PM

• You didn't indicate if you were using SQL 2000 or SQL 2005.

This is an example of a 'single pass' collection and display of data -it may give you an idea of how to improve your current procedure. (This process will work in both SQL 2000 and SQL 2005. It uses the Northwind database.)

Code Snippet

IF EXISTS
(  SELECT ROUTINE_NAME
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_NAME = 'spAnnualSalesByMonth'
)
DROP PROCEDURE dbo.spAnnualSalesByMonth
GO

CREATE PROCEDURE dbo.spAnnualSalesByMonth
AS
SELECT
max( dt.[Year] )                             AS 'Year'
, convert( varchar(12),  max( dt.Jan ), 1 )    AS 'Jan'
, convert( varchar(12),  max( dt.Feb ), 1 )    AS 'Feb'
, convert( varchar(12),  max( dt.Mar ), 1 )    AS 'Mar'
, convert( varchar(12),  max( dt.Apr ), 1 )    AS 'Apr'
, convert( varchar(12),  max( dt.May ), 1 )    AS 'May'
, convert( varchar(12),  max( dt.Jun ), 1 )    AS 'Jun'
, convert( varchar(12),  max( dt.Jul ), 1 )    AS 'Jul'
, convert( varchar(12),  max( dt.Aug ), 1 )    AS 'Aug'
, convert( varchar(12),  max( dt.Sep ), 1 )    AS 'Sep'
, convert( varchar(12),  max( dt.Oct ), 1 )    AS 'Oct'
, convert( varchar(12),  max( dt.Nov ), 1 )    AS 'Nov'
, convert( varchar(12),  max( dt.[Dec] ), 1 )  AS 'Dec'
FROM
(  SELECT
datepart( year,  o.OrderDate )                                                                  AS 'Year'
, datepart( month, o.OrderDate )                                                                  AS 'Month'
, CASE when ( datepart( month, o.OrderDate )) = 1  THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Jan'
, CASE when ( datepart( month, o.OrderDate )) = 2  THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Feb'
, CASE when ( datepart( month, o.OrderDate )) = 3  THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Mar'
, CASE when ( datepart( month, o.OrderDate )) = 4  THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Apr'
, CASE when ( datepart( month, o.OrderDate )) = 5  THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'May'
, CASE when ( datepart( month, o.OrderDate )) = 6  THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Jun'
, CASE when ( datepart( month, o.OrderDate )) = 7  THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Jul'
, CASE when ( datepart( month, o.OrderDate )) = 8  THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Aug'
, CASE when ( datepart( month, o.OrderDate )) = 9  THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Sep'
, CASE when ( datepart( month, o.OrderDate )) = 10 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Oct'
, CASE when ( datepart( month, o.OrderDate )) = 11 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Nov'
, CASE when ( datepart( month, o.OrderDate )) = 12 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Dec'
FROM Orders o
JOIN [Order Details] od
ON o.OrderID = od.OrderID
GROUP BY
datepart( year,  o.OrderDate )
, datepart( month, o.OrderDate )
) dt
GROUP BY dt.[Year]
ORDER BY dt.[Year]
GO

EXECUTE dbo.spAnnualSalesByMonth

(Output clipped for display)

Year        Jan          Feb          Mar          Apr          May          Jun          Jul
----------- ------------ ------------ ------------ ------------ ------------ ------------ ------------
1996        0.00         0.00         0.00         0.00         0.00         0.00         30,192.10
1997        66,692.80    41,207.20    39,979.90    55,699.39    56,823.70    39,088.00    55,464.93
1998        100,854.72   104,561.95   109,825.45   134,630.56   19,898.66    0.00         0.00

However, if you are using SQL 2005, there may be more efficency gained by using the new PIVOT operator. (This is untested.)

Code Snippet

DECLARE @LogSummary table

LogYear   char(4) NOT NULL,
LogMonth  char(2) NOT NULL,
)

INSERT INTO @LogSummary
SELECT DISTINCT
year( Logged),
month( Logged )
FROM ServiceLog