none
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

    Monday, May 7, 2007 6:35 PM

Answers

  • 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

     

     

    Tuesday, May 8, 2007 12:36 AM
    Moderator