bes7252


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




Re: Faster way to do this?

Arnie Rowland


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
( IPAddress varchar(15),

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


INSERT INTO @LogSummary
SELECT DISTINCT
IPAddress,
year( Logged),
month( Logged )
FROM ServiceLog
WHERE Method = 'LOGIN'


SELECT *
FROM @LogSummary
PIVOT ( count( IPAddress ) FOR LogMonth
IN ( [01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12] )) AS LogPivot