为什么在MS SQL Server中有些日期的性能比其他日期差

Question

我在MS SQL Server中查询名称和一些日期相关信息，取决于两个日期，一个开始日期和一个结束日期。

问题是，我并不总是得到相同的性能。每当我在日期之间请求某事时;

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

性能非常好。我能在几秒内得到结果。当我在这些日期之间请求某些东西时，例如，

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

性能是…不太好，每个查询需要20-28秒。请注意，表现良好的日期之间的间隔是一年以上，而后者是20天。

是否有什么特别的原因(可能与DATETIME的工作方式有关)?

编辑:查询，

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

(部分)表:

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)

Answer 1

我还以为是统计问题呢。

当您只选择最近的日期时，这些日期可能未在统计数据中表示，因为尚未达到触发自动更新的阈值。