Solved

Slow Query for Unknown Reason

Posted on 2011-09-12
10
310 Views
Last Modified: 2012-05-12
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];

Open in new window

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.
0
Comment
Question by:Kavalleri
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 36527282
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.
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 36527325
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]

Open in new window

0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36527336
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.
0
 
LVL 1

Author Comment

by:Kavalleri
ID: 36527338
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.
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 36527372
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]

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:Kavalleri
ID: 36527410
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?
0
 
LVL 1

Author Comment

by:Kavalleri
ID: 36527422
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.
0
 
LVL 39

Expert Comment

by:appari
ID: 36527751
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
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];

Open in new window


replace Number with 2 will return you 61 rows where as using Number returns 364.
0
 
LVL 39

Expert Comment

by:appari
ID: 36527777
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];

Open in new window

0
 
LVL 8

Accepted Solution

by:
Ghunaima earned 500 total points
ID: 36534520
Replacing Numbers.Number with any fixed no is equivalent to having a single row in Numbers table so the time taken will be less as compared to when Numbers contains more than one row.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now