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
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
No comments:
Post a Comment