2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kiran
  • 71
  • 1
  • 14

1 Answers1

6

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'
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • after executing the outer query then i'll get list of dates. then for that dates i am extracting month and getting total days for that extracted month – Kiran Oct 29 '15 at 05:16
  • not sure if I understand your comment, did this work or not? Or maybe I didnt understand your question? – Juan Carlos Oropeza Oct 29 '15 at 05:17
  • say date is '2000-01-01 00:00:00' so from that date i'm extracting month and for that month i should tell how many days in that particular month.. – Kiran Oct 29 '15 at 05:20
  • month is jan so i should display 31days. to display that no of days im counting month jan for that particular year in calendar table. – Kiran Oct 29 '15 at 05:22
  • 1
    If you want know how many days are in a month is a very easy way to do it. Check this question http://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server – Juan Carlos Oropeza Oct 29 '15 at 05:24
  • hope i cleared.. and similarly here i have date range. here i'll get list of dates between that range. so for each date i should extract month and display no of days – Kiran Oct 29 '15 at 05:24
  • Your calendar table only have `date` field and all 365 dates from the year? – Juan Carlos Oropeza Oct 29 '15 at 05:25
  • can you explain what you got how? Did my answer solve your question or your find it in your own How much time take now? I hope at least give you enought hints to deserve the correct answer. But if you find another you should share it. :) – Juan Carlos Oropeza Oct 29 '15 at 05:33
  • datediff(day, c.dt, dateadd(month, 1, c.dt)) i used this instead of extracting month always from the list of dates – Kiran Oct 29 '15 at 05:47