Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
andros30
Yak Posting Veteran
80 Posts |
 Posted - 2008-10-31 : 18:07:53
|
| How can I optimize the following query. It is currently taking over 2 minutes to run. I'm trying to get a total of kept appointment in past due status by offices.declare @startdate datetimedeclare @enddate datetimeset @startdate = '09/01/08'set @enddate = '10/01/08'select c.ConfigECode as Office,sum(case when ap.dt_appt >= @startdate and ap.dt_appt < @enddate --define search date range.and ap.stat_cd in (1,7) --check for kept appointment status.and ap.PatsAcctStatus in (4,5,6,7,1,2) --check for past due status.then 1 else 0 end) as KeptApptfrom tbl_appt ap with(nolock)inner join tConfig con c.ConfigOfficeNumber = ap.OfficeNumbergroup by c.ConfigECodehaving sum(case when ap.dt_appt >= @startdate and ap.dt_appt < @enddate and ap.stat_cd in (1,7) and ap.PatsAcctStatus in (4,5,6,7,1,2) then 1 else 0 end) > 0 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-31 : 18:32:05
|
| what does your execution plan say? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
andros30
Yak Posting Veteran
80 Posts |
Posted - 2008-11-03 : 09:45:37
|
quote:
Originally posted by sodeep
what does your execution plan say?
I really don't know how to interpret the execution plan. All I know is this:17% Aggregate on c.ConfigECode20% Inner Join on ap.PatsAcctStatus, ap.stat_cd, ap.dt_appt and c.ConfigECode60% Clustered Index Scan on tbl_appt3% Compute Scalar |
|
|
|
andros30
Yak Posting Veteran
80 Posts |
|
|
|
|
|
|
|
Long execution time
Tara Kizer