Better way to calculate utilisation

Question

I have a rather complicated (and very inefficient) way of getting utilisation from a large list of periods (Code below).

Currently I'm running this for a period of 8 weeks and it's taking between 30 and 40 seconds to return data.

I need to run this regularly for periods of 6 months, 1 year and two years which will obviously take a massive amount of time.

Is there a smarter way to run this query to lower the number of table scans? I have tried several ways of joining the data, all seem to return junk data.

I've tried to comment the code as much as I can but if anything is unclear let me know.

Table Sizes:

[Stock]        ~12,000 records
[Contitems]    ~90,000 records

Pseudocode for clarity:

For each week between Start and End:
    Get list of unique items active between dates (~12,000 rows)
        For each unique item
            Loop through ContItems table (~90,000 rows)
            Return matches
        Group
    Group
Return results

The Code

DECLARE @WEEKSTART DATETIME; -- Used to pass start of period to search
DECLARE @WEEKEND DATETIME; -- Used to pass end of period to search
DECLARE @DC DATETIME; -- Used to increment dates
DECLARE @INT INT; -- days to increment for each iteration (7 = weeks)
DECLARE @TBL TABLE(DT DATETIME, SG VARCHAR(20), SN VARCHAR(50), TT INT, US INT); -- Return table

SET @WEEKSTART = '2012-05-01'; -- Set start of period
SET @WEEKEND = '2012-06-25'; -- Set end of period
SET @DC = @WEEKSTART; -- Start counter at first date
SET @INT = 7; -- Set increment to weeks

WHILE (@DC < @WEEKEND) -- Loop through dates every [@INT] days (weeks)
BEGIN
    SET @DC = DATEADD(D,@INT,@DC); -- Add 7 days to the counter

        INSERT INTO @TBL (DT, SG, SN, TT, US) -- Insert results from subquery into return table
        SELECT @DC, SUB.GRPCODE, SubGrp.NAME, SUM(SUB.TOTSTK), SUM(USED)

        FROM
        (
        SELECT STK.GRPCODE, 1 AS TOTSTK, CASE (SELECT COUNT(*) 
                                                           FROM ContItems -- Contains list of hires with  a start and end date
                                                           WHERE STK.ITEMNO = ContItems.ITEMNO -- unique item reference
                                                           AND ContItems.DELDATE <= DATEADD(MS,-2,DATEADD(D,@INT,@DC)) -- Hires starting before end of week searching
                                                           AND (ContItems.DOCDATE#5 >= @DC -- Hires ending after start of week searching
                                                                OR ContItems.DOCDATE#5 = '1899-12-30 00:00:00.000')) -- Or hire is still active
                                                     WHEN 0 THEN 0 -- None found return zero
                                                     WHEN NULL THEN 0 -- NULL return zero
                                                     ELSE 1 END AS USED -- Otherwise return 1

        FROM Stock STK - List of unique items

        WHERE [UNIQUE] = 1 AND [TYPE] != 4 -- Business rules
        AND DATEPURCH < @DC AND (DATESOLD = '1899-12-30 00:00:00.000' OR DATESOLD > DATEADD(MS,-2,DATEADD(D,@INT,@DC))) -- Stock is valid between selected week
        ) SUB
        INNER JOIN SubGrp -- Used to get 'pretty' names
        ON SUB.GRPCODE = SubGrp.CODE
        GROUP BY SUB.GRPCODE, SubGrp.NAME






END

-- Next section gets data from temp table
SELECT SG, SN, SUM(TT) AS TOT, SUM(US) AS USED, CAST(SUM(US) AS FLOAT) / CAST(SUM(TT) AS FLOAT) AS UTIL
FROM @TBL
GROUP BY SG, SN
ORDER BY TOT DESC

Answer 1

I have two suggestions.

First, rewrite the query to move the "select" statement from the case statement to the from clause:

SELECT @DC, SUB.GRPCODE, SubGrp.NAME, SUM(SUB.TOTSTK), SUM(USED)
FROM (SELECT STK.GRPCODE, 1 AS TOTSTK,
              (CASE MAX(Contgrp.cnt) -- Or hire is still active
                     WHEN 0 THEN 0 -- None found return zero
                     WHEN NULL THEN 0 -- NULL return zero
                     ELSE 1
                END) AS USED -- Otherwise return 1
        FROM Stock STK left outer join -- List of unique items
             (SELECT itemno, COUNT(*) as cnt
              FROM ContItems -- Contains list of hires with  a start and end date
              WHERE ContItems.DELDATE <= DATEADD(MS,-2,DATEADD(D,@INT,@DC)) AND -- Hires starting before end of week searching
                     (ContItems.DOCDATE#5 >= @DC OR -- Hires ending after start of week searching
                      ContItems.DOCDATE#5 = '1899-12-30 00:00:00.000'
                     )
              group by ITEMNO
             ) ContGrp
             on STK.ITEMNO = ContItems.ITEMNO
        WHERE [UNIQUE] = 1 AND [TYPE] != 4 AND -- Business rules
              DATEPURCH < @DC AND (DATESOLD = '1899-12-30 00:00:00.000' OR DATESOLD > DATEADD(MS,-2,DATEADD(D,@INT,@DC))) -- Stock is valid between selected week
       ) SUB INNER JOIN SubGrp -- Used to get 'pretty' names
       ON SUB.GRPCODE = SubGrp.CODE
 GROUP BY SUB.GRPCODE, SubGrp.NAME 

In doing this, I found a something suspicious. The case statement is operating at the level of "ItemNo", but the grouping is by "GrpCode". So, the "Count(*)" is really returning the sum at the group level. Is this what you intend?

The second is to dispense with the WHILE loop, if you have multiple weeks. To do this, you just need to convert DatePurch to an appropriate week. However, if the code usually runs on just one or two weeks, this effort may not help very much.

Answer 2

Well, replacing the DATEADD functions in the WHERE clauses at first.

You already have

SET @DC = DATEADD(D,@INT,@DC);

Why not declare another local variable for deletion date:

WHILE (@DC < @WEEKEND) -- Loop through dates every [@INT] days (weeks)
BEGIN
    SET @DC = DATEADD(D,@INT,@DC); 

    DECLARE @DeletionDate DATETIME =  DATEADD(MS,-2,DATEADD(D,@INT,@DC));

And use it in the case statement:

CASE (SELECT COUNT(*) .... AND ContItems.DELDATE <= @DeletionDate ....

And also in the outer where clause...

Then you need to make sure that you have correctly indexed your tables.