Why some dates give worse performance than other in MS SQL Server

Question

I have a query in MS SQL Server asking for name and some date-related information, depending on two dates, a start- and an enddate.

The problem is, I´m not always getting the same performance. Whenever I request something between the dates;

2010-07-01 00:00:00.000 and
2011-07-21 23:59:59.999

the performance is excellent. I get my result within mseconds. When I request something between these dates, for example,

2011-07-01 00:00:00.000 and
2011-07-21 23:59:59.999

the performance is.. less than good, taking between 20-28 seconds for each query. Do note how the dates giving good performance is more than a year between, while the latter is 20 days.

Is there any particular reason (maybe related to how DATETIME work) for this?

EDIT: The query,

SELECT ENAME, 
    SUM(CASE DATE WHEN 0 THEN 1 ELSE 0 END) AS U2, 
    SUM(CASE DATE WHEN 1 THEN 1 ELSE 0 END) AS B_2_4, 
    SUM(CASE DATE WHEN 2 THEN 1 ELSE 0 END) AS B_4_8, 
    SUM(CASE DATE WHEN 3 THEN 1 ELSE 0 END) AS B_8_16, 
    SUM(CASE DATE WHEN 4 THEN 1 ELSE 0 END) AS B_16_24, 
    SUM(CASE DATE WHEN 5 THEN 1 ELSE 0 END) AS B_24_48, 
    SUM(CASE DATE WHEN 6 THEN 1 ELSE 0 END) AS O_48, 
    SUM(CASE DATE WHEN 7 THEN 1 ELSE 0 END) AS status, 
    AVG(AVG) AS AVG, 
    SUM(DATE) AS TOTAL

FROM 
    (SELECT ENAME, 
            (CASE 
                WHEN status = 'Ã–ppet' THEN 7 
                WHEN DATE < 48 THEN 
                    (CASE WHEN DATE BETWEEN 0 AND 2 THEN 0 
                    WHEN DATE BETWEEN 2 AND 4 THEN 1 
                    WHEN DATE BETWEEN 4 AND 8 THEN 2 
                    WHEN DATE BETWEEN 8 AND 16 THEN 3 
                    WHEN DATE BETWEEN 16 AND 24 THEN 4 
                    WHEN DATE BETWEEN 24 AND 48 THEN 5 
                    ELSE - 1 END) 
            ELSE 6 END) AS DATE, 
            DATE AS AVG
    FROM 
        (SELECT DATEDIFF(HOUR, cases.date, status.date) AS DATE, 
        extern.name AS ENAME, 
        status.status
    FROM 
        cases INNER JOIN
        status ON cases.id = status.caseid 
                AND status.date =
                    (SELECT MAX(date) AS Expr1
                    FROM status AS status_1
                    WHERE (caseid = cases.id)
                    GROUP BY caseid) INNER JOIN
                    extern ON cases.owner = extern.id
                    WHERE (cases.org = 'Expert') 
                        AND (cases.date BETWEEN '2009-01-15 09:48:25.633' 
                        AND '2011-07-21 09:48:25.633'))
    AS derivedtbl_1) 
AS derivedtbl_2
GROUP BY ENAME
ORDER BY ENAME

(parts of) The tables:

Extern
    -ID (->cases.owner)
    -name
Cases
    -Owner (->Extern.id)
    -id (->status.caseid)
    -date (case created at this date)
Status
    -caseid (->cases.id)
    -Status
    -Date (can be multiple, MAX(status.date) gives us date when 
     status was last changed)

Why some dates give worse performance than other in MS SQL Server

Answers (1)

Related Questions