Kavalleri
asked on
Slow Query for Unknown Reason
I'm trying to execute the following query but I can't figure out why it takes about 30 seconds to run:
The [Numbers] table has a primary key [Number] column that is an integer with values from 0 to 365.
If I replace the
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.
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')
with
<= 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.
Well. ORDER BY is always slow down your query. Try to remove order by. If still you want order by then create a TABLE variable and place the selected records. Then apply ORDER BY on the Table variable.
Issuing two SELECTs on Patients table is another case for slow speed , try attached code it will reduce the time a bit
select thedate, sum([nextcount]) NextCount, sum([POECount]) [POECount] from
(SELECT CONVERT(varchar(10), DATEADD(D, [Numbers].[Number], '09/12/2011'), 101) As [TheDate],
case when [Patients].[NextExam] <= DATEADD(D, [Patients].[Number], '09/12/2011') then 1 else 0 end AS [NextCount],
case when [Patients].[POExam] <= DATEADD(D, [Patients].[Number], '09/12/2011') then 1 else 0 end AS [POECount]
FROM [Numbers], [Patients] WHERE [Numbers].[Number] < DATEDIFF(D, '09/12/2011', DATEADD(M, 2, '09/12/2011'))
) x
group by thedate
ORDER BY [TheDate]
the better way in my experience for good performance is
a) create a view for often used queries or
b) create stored procedures which accepts parameters as input and sends the results back as output. there should be a difference in performance.
a) create a view for often used queries or
b) create stored procedures which accepts parameters as input and sends the results back as output. there should be a difference in performance.
ASKER
I have attempted dropping the ORDER BY clause while I was trying to optimize it myself but it did not run any faster. Also I discovered all I need to do is replace the [Number] with <any number> in the DATEADD function in the WHERE clause and it finishes in less than a second. So I'm confused as to why this would make that big a difference.
Thanks.
Thanks.
What is the structure of the tables? DO you have a column named Number in Patients table or you want to use the Number from Numbers table to be used in your internal queries. If that is the case Try this
select thedate, sum([nextcount]) NextCount, sum([POECount]) [POECount] from
(SELECT CONVERT(varchar(10), DATEADD(D, [Numbers].[Number], '09/12/2011'), 101) As [TheDate],
case when [Patients].[NextExam] <= DATEADD(D, [Numbers].[Number], '09/12/2011') then 1 else 0 end AS [NextCount],
case when [Patients].[POExam] <= DATEADD(D, [Numbers].[Number], '09/12/2011') then 1 else 0 end AS [POECount]
FROM [Numbers], [Patients] WHERE [Numbers].[Number] < DATEDIFF(D, '09/12/2011', DATEADD(M, 2, '09/12/2011'))
) x
group by thedate
ORDER BY [TheDate]
ASKER
Ghunaima,
The [Numbers].[Number] column is purely to help iterate thru each date between the start and end date in question. There is no [Number] column in the [Patients] table.
Thanks for the query but it still took about 30 seconds to execute. I then took the same query and replaced the [Numbers].[Number] in the CASE statement with just any random number and it executed in a fraction of a second. I just don't know why the optimizer treats those values any different.
Does anyone know a better way to iterate thru a date range if those dates are not specifically in the database?
The [Numbers].[Number] column is purely to help iterate thru each date between the start and end date in question. There is no [Number] column in the [Patients] table.
Thanks for the query but it still took about 30 seconds to execute. I then took the same query and replaced the [Numbers].[Number] in the CASE statement with just any random number and it executed in a fraction of a second. I just don't know why the optimizer treats those values any different.
Does anyone know a better way to iterate thru a date range if those dates are not specifically in the database?
ASKER
shasunder:
I have attempted creating a stored procedure for this and surprisingly it did not run any better. I have not attempted creating a view for this so I will have to give that a try and let you know.
Thanks.
I have attempted creating a stored procedure for this and surprisingly it did not run any better. I have not attempted creating a view for this so I will have to give that a try and let you know.
Thanks.
what are you exactly trying to do? explain us with sample data.
data returned by fixing a number and using Number column value in dateadd function will return you different results.
for testing try this
replace Number with 2 will return you 61 rows where as using Number returns 364.
data returned by fixing a number and using Number column value in dateadd function will return you different results.
for testing try this
declare @Numbers table(number int)
declare @i int
set @i=0
while @i<365
begin
insert into @Numbers select @i
set @i=@i+1
end
--select * from @Numbers
SELECT
Number,
DATEDIFF(D, '09/12/2011', DATEADD(M, Number, '09/12/2011')) ,
CONVERT(varchar(10), DATEADD(D, [Number], '09/12/2011'), 101) As [TheDate]
FROM @Numbers [Numbers] WHERE [Number] < DATEDIFF(D, '09/12/2011', DATEADD(M, Number, '09/12/2011'))
ORDER BY [TheDate];
replace Number with 2 will return you 61 rows where as using Number returns 364.
also try this
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] ORDER BY [TheDate];
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.