CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Kiran
Kiran

Reputation: 71

How to reduce the query execution time

this is the execution planThe image shows the results for the query and its taking 25secs for ~7500 rows

 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'

the above query is for getting number of days of particular month for a particular date. here iam giving date range and for all the dates between the range iam just showing the days of that month.

The image shows the results for the query and its taking 25secs for ~7500 rows. can someone help me to reduce the time.

Upvotes: 2

Views: 9439

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Try this one. Here you calculate the total only once instead of 7500 times. Also create the index for dt field

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'

Upvotes: 6

Related Questions