sql - 如何减少查询执行时间

Question

 SELECT dt AS Date
,monthname
,dayname
,(
    SELECT COUNT(1)
    FROM Calendar
    WHERE DATEPART(MM, dt) = DATEPART(MM, c.dt)
        AND DATEPART(YEAR, dt) = DATEPART(YEAR, c.dt)
    ) AS daysInMonth
FROM Calendar AS c
WHERE dt BETWEEN '2000-01-01 00:00:00'
    AND '2020-02-01 00:00:00'

上面的查询是为了获取特定日期的特定月份的天数。这里我给出日期范围，对于范围之间的所有日期，我只显示那个月的天数。

图像显示了查询的结果，它用了 25 秒的时间查询了大约 7500 行。谁能帮我缩短时间。

Answer 1

试试这个。在这里你只计算总数一次而不是 7500 次。同时为 dt 字段创建索引

with monthCount as (
      SELECT DATEPART(YEAR, dt) as m_year,
             DATEPART(MM, dt) as m_month
             COUNT(1) as total
      FROM Calendar
      GROUP BY 
            DATEPART(YEAR, dt),
            DATEPART(MM, dt)
)
SELECT dt AS Date
       ,monthname
       ,dayname
       ,total
FROM Calendar C
JOIN monthCount M
    on DATEPART(YEAR, C.dt) = M.m_year
   and DATEPART(MM, C.dt) = M.m_month
WHERE C.dt BETWEEN '2000-01-01 00:00:00'
               AND '2020-02-01 00:00:00'