I'm trying to execute the following query but I can't figure out why it takes about 30 seconds to run:
SELECT CONVERT(varchar(10), DATEADD(D, [Number], '09/12/2011'), 101) As [TheDate],
(SELECT COUNT([PatientID]) FROM [Patients] WHERE [NextExam] <= DATEADD(D, [Number], '09/12/2011')) AS [NextCount],
(SELECT COUNT([PatientID]) FROM [Patients] WHERE [POEExam] <= DATEADD(D, [Number], '09/12/2011')) AS [POECount]
FROM [Numbers] WHERE [Number] < DATEDIFF(D, '09/12/2011', DATEADD(M, 2, '09/12/2011')) ORDER BY [TheDate];
All I'm trying to do is iterate thru two months of data and collecting the number of patient exams that are due by the date in question. The actual date ('09/12/2011') is the only value that is dynamically inputted at run-time.
The [Numbers] table has a primary key [Number] column that is an integer with values from 0 to 365.
If I replace the
<= DATEADD(D, [Number], '09/12/2011')
<= DATEADD(D, <any number>, '09/12/2011')
the query finishes in a fraction of a second.
It also does not seem to matter if I iterate thru a month or an entire year, it still takes about 30 seconds.
If anyone has any thoughts on how I can optimize or rewrite the query to execute faster I would greatly appreciate it.