更改WHERE子句中的比较器会对查询性能产生灾难性的影响

Question

我在一个SQL SERVER 2005数据库上运行一个怪异的查询，它的行为非常奇怪。我在最外面的select语句的WHERE子句中有两个条件，将一个字段与一个常量日期进行比较。当常量日期相同(精确到秒)或它们的日期部分不相等时，查询将在2秒内运行。当日期部分相同但时间部分不同时，查询大约需要7分钟才能完成。具体地说，拥有WHERE子句

WHERE
  d.date >= '2011-11-07 00:00:00' AND
  d.date <= '2011-11-08 11:59:59'

运行良好，符合预期。将WHERE子句更改为

WHERE
  d.date >= '2011-11-07 00:00:00' AND
  d.date <= '2011-11-07 11:59:59'

导致查询花费几分钟的时间。

我还注意到，当我关闭Agent_Hours表上的索引时，日期相同的坏情况会将查询时间减少到25秒，这仍然比日期不同时要长得多，但没有那么多。

下面是完整的查询以供参考(问题中的WHERE子句在最后)：

SELECT
  s.transaction_id AS 'transaction',
  s.created_on AS transaction_date,
  s.first_name + ' ' + s.Last_Name AS customer_name,
  a.name AS agent_name,
  a.phantom AS phantom,
  a.team AS agent_team,
  a.id AS agent_number,
  h.hours,
  h2.hours_today,
  d.*
FROM
  (SELECT
     agents.first_name + ' ' + agents.last_name AS name,
     agents.id AS id,
     agents.phantom AS phantom,
     transient.value AS team,
     transient.start_date AS team_start_date,
     transient.end_date AS team_end_date
   FROM
       Agents.dbo.Agent_Static AS agents
     JOIN
       Agents.dbo.Agent_Transient AS transient
     ON transient.agent = agents.id
   WHERE
     transient.field = 'team') AS a
  LEFT JOIN Agents.dbo.Agent_Daily AS d
    ON d.agent = a.id
  LEFT JOIN (SELECT
               agent_hours.agent AS agent,
               dates.date AS date,
               CAST(COUNT(*) AS FLOAT) / 4 AS hours
             FROM
                 Agents.dbo.Agent_Hours AS agent_hours
               JOIN
                 (SELECT
                    DISTINCT CONVERT(
                               VARCHAR(10),
                               hour_worked,
                               101)
                               AS date
                  FROM
                    Agents.dbo.Agent_Hours) AS dates
               ON dates.date = CONVERT(
                                 VARCHAR(10),
                                 agent_hours.hour_worked,
                                 101)
             WHERE
               (status = 'Phone' OR
                status = 'Meeting')
             GROUP BY
               agent_hours.agent,
               dates.date) AS h
    ON h.agent = a.id AND
       h.date = d.date
  LEFT JOIN (SELECT
               agent_hours.agent AS agent,
               dates.date AS date,
               CAST(COUNT(*) AS FLOAT) / 4 AS hours_today
             FROM
                 Agents.dbo.Agent_Hours AS agent_hours
               JOIN
                 (SELECT
                    DISTINCT CONVERT(
                               VARCHAR(10),
                               hour_worked,
                               101)
                               AS date
                  FROM
                    Agents.dbo.Agent_Hours) AS dates
               ON dates.date = CONVERT(
                                 VARCHAR(10),
                                 agent_hours.hour_worked,
                                 101)
             WHERE
               (status = 'Phone' OR
                status = 'Meeting') AND
               CONVERT(
                 VARCHAR(10),
                 CAST('11/09/2011 13:01' AS DATETIME),
                 101) = CONVERT(
                          VARCHAR(10),
                          agent_hours.hour_worked,
                          101) AND
               CONVERT(
                 VARCHAR(10),
                 CAST('11/09/2011 13:01' AS DATETIME),
                 114) > CONVERT(
                          VARCHAR(10),
                          agent_hours.hour_worked,
                          114)
             GROUP BY
               agent_hours.agent,
               dates.date) AS h2
    ON h2.agent = a.id AND
       h2.date = d.date
  LEFT JOIN sale_transactions AS s
    ON a.id = s.agent_hermes_id AND
       s.created_on >= a.team_start_date AND
       s.created_on <= a.team_end_date AND
       CONVERT(
         VARCHAR(10),
         d.date,
         101) = CONVERT(
                  VARCHAR(10),
                  s.created_on,
                  101)
  LEFT JOIN sold_phrases AS p
    ON s.Transaction_ID = p.transaction_id
WHERE
  d.date >= '2011-11-07 00:00:00' AND
  d.date <= '2011-11-07 11:59:59'

Answer 1

作为一般规则，在SQL中询问性能问题时，请始终发布确切的表定义，包括所有索引。

我看不出这两种情况之间有什么不同，但考虑到您的解释，这是可能发生的情况:日期范围的基数估计可能会触发index tipping point，并且您会得到截然不同的执行计划。这些问题最好通过使用计划指南来解决，请参阅Optimizing Queries in Deployed Applications by Using Plan Guides。您应该能够确认问题是否确实是计划所致，请参阅Displaying Graphical Execution Plans (SQL Server Management Studio)。

Answer 2

这可能是一个微优化，但您是否考虑过将获取日期部分的方式从datetime更改为DATEADD(dd, 0, DATEDIFF(dd, 0, datetime_format))。它通常比convert函数更快。

SELECT
  s.transaction_id AS 'transaction',
  s.created_on AS transaction_date,
  s.first_name + ' ' + s.Last_Name AS customer_name,
  a.name AS agent_name,
  a.phantom AS phantom,
  a.team AS agent_team,
  a.id AS agent_number,
  h.hours,
  h2.hours_today,
  d.*
FROM (SELECT
     agents.first_name + ' ' + agents.last_name AS name,
     agents.id AS id,
     agents.phantom AS phantom,
     transient.value AS team,
     transient.start_date AS team_start_date,
     transient.end_date AS team_end_date
   FROM
       Agents.dbo.Agent_Static AS agents
     JOIN
       Agents.dbo.Agent_Transient AS transient
     ON transient.agent = agents.id
   WHERE
     transient.field = 'team'
) AS a
LEFT JOIN Agents.dbo.Agent_Daily AS d ON d.agent = a.id
LEFT JOIN (
    SELECT
        agent_hours.agent AS agent, 
        dates.date AS date,
        COUNT(*) / 4.0 AS hours
    FROM Agents.dbo.Agent_Hours AS agent_hours
    JOIN (
        SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, hour_worked)) as date
        FROM Agents.dbo.Agent_Hours GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, hour_worked))
    ) AS dates ON dates.date = DATEADD(dd, 0, DATEDIFF(dd, 0, agent_hours.hour_worked))
    WHERE (status = 'Phone' OR status = 'Meeting')
    GROUP BY agent_hours.agent, dates.date
) AS h ON h.agent = a.id AND h.date = d.date
LEFT JOIN (
    SELECT
        agent_hours.agent AS agent,
        dates.date AS date, 
        COUNT(*) / 4.0 AS hours_today
    FROM Agents.dbo.Agent_Hours AS agent_hours
    JOIN (
        SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, hour_worked)) as date
    FROM Agents.dbo.Agent_Hours GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, hour_worked))
    ) AS dates ON dates.date = DATEADD(dd, 0, DATEDIFF(dd, 0, agent_hours.hour_worked))
    WHERE 
        (status = 'Phone' OR status = 'Meeting') AND
        agent_hours.hour_worked >=
        DATEADD(dd, 0, DATEDIFF(dd, 0, CAST('11/09/2011 13:01' AS DATETIME)))
        AND
        agent_hours.hour_worked <
        CAST('11/09/2011 13:01' AS DATETIME)
    GROUP BY agent_hours.agent, dates.date
    ) AS h2 ON h2.agent = a.id AND h2.date = d.date

  LEFT JOIN sale_transactions AS s
    ON a.id = s.agent_hermes_id AND
       s.created_on >= a.team_start_date AND
       s.created_on <= a.team_end_date AND
        DATEADD(dd, 0, DATEDIFF(dd, 0, d.date))
        = 
        DATEADD(dd, 0, DATEDIFF(dd, 0, s.created_on))
  LEFT JOIN sold_phrases AS p
    ON s.Transaction_ID = p.transaction_id
WHERE
  d.date >= '2011-11-07 00:00:00' AND
  d.date <= '2011-11-07 11:59:59'

更重要的(正如Remus Rusanu已经写过的)是索引。执行这两个查询并检查在更快的查询中使用了哪些索引，并强制SQL Server始终使用它们。您可以使用with(index(index_name))来完成此操作。