Issue with DateCreated

Question

I have a table dbo.participation:

ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,  
User VARCHAR(MAX) NOT NULL,  
ParticipationLevel TINYINT NOT NULL,  
Selector VARCHAR(MAX) NOT NULL,  
DateCreated DATETIME NOT NULL

I created the code below but unfortunately it shows bad performance for @DateStart and @DateStop

SELECT 
    dateadd(month, datediff(month, 0, DateCreated), 0) AS MDate
    ,COUNT(CASE WHEN ParticipationLevel >= 10 THEN Selector ELSE NULL END) AS ParticipationLevel1
    ,COUNT(CASE WHEN ParticipationLevel >= 30 THEN Selector ELSE NULL END) AS ParticipationLevel2
FROM 
    Participation
WHERE 
    (@DateStart IS NULL OR (@DateStart IS NOT NULL 
                            AND DateCreated >= @DateStart)) 
    AND (@DateEnd IS NULL OR (@DateEnd IS NOT NULL 
                              AND DateCreate < @DateEnd))
GROUP BY 
    Dateadd(month, datediff(month, 0, DateCreate), 0)

Do you happen to have any ideas how to improve my code or alternatively how to modify the table to improve performance?

Answer 1

You need an index along the following lines

CREATE INDEX ix
  ON dbo.Participation(DateCreated)
  INCLUDE (ParticipationLevel);

And you should rewrite the query to get rid of the OR and to avoid the unnecessary reference to a column defined as NOT NULL.

(Note a simple COUNT(Selector) would not look up the value as SQL Server recognizes it can't be NULL but wrapping in an expression defeats this logic)

SELECT DATEADD(month, DATEDIFF(month, 0, DateCreated), 0) AS MDate,
       COUNT(CASE
               WHEN ParticipationLevel >= 10 THEN 1
             END)                                         AS ParticipationLevel1,
       COUNT(CASE
               WHEN ParticipationLevel >= 30 THEN 1
             END)                                         AS ParticipationLevel2
FROM   Participation
WHERE  DateCreated >= ISNULL(@DateStart, '17530101')
       AND DateCreated <= ISNULL(@DateEnd, '99991231')
GROUP  BY DATEDIFF(month, 0, DateCreated) 

This can give a plan with a seek as below

Note that it would be possible to get rid of the sort by processing chunks of the index a month at the time (possibly in a recursive CTE) but this may be overkill.

Code for that could look something like

/*Cheap to find out from the index*/

IF @DateStart IS NULL
  SELECT @DateStart = MIN(DateCreated)
  FROM   dbo.Participation

IF @DateStart IS NULL
  SELECT @DateEnd = MAX(DateCreated)
  FROM   dbo.Participation

/*Adjust to start of month*/
SELECT @DateStart = DATEADD(month, DATEDIFF(month, 0, @DateStart), 0),
       @DateEnd = DATEADD(month, 1 + DATEDIFF(month, 0, @DateEnd), 0);


WITH Dates
     AS (SELECT @DateStart AS MDate
         UNION ALL
         SELECT dateadd(MONTH, 1, MDate) AS MDate
         FROM   Dates
         WHERE  dateadd (MONTH, 1, MDate) <= @DateEnd)
SELECT D.MDate,
       CA.ParticipationLevel1,
       CA.ParticipationLevel2
FROM   Dates D
       CROSS APPLY (SELECT COUNT(CASE
                                   WHEN ParticipationLevel >= 10
                                     THEN 1
                                 END) AS ParticipationLevel1,
                           COUNT(CASE
                                   WHEN ParticipationLevel >= 30
                                     THEN 1
                                 END) AS ParticipationLevel2
                    FROM   Participation P WITH (INDEX = ix)
                    WHERE  P.DateCreated >= D.MDate
                           AND P.DateCreated < DATEADD(MONTH, 1, D.MDate)
                    GROUP  BY () /* So no grouping row returned for empty months */
            ) CA(ParticipationLevel1, ParticipationLevel2)
OPTION (MAXRECURSION 0); 

Which gives a plan with repeated seeks and no sorts