October 24, 2013 at 11:33 am
Below are two scenarios. The first takes minutes to complete and the second takes seconds. Is there a reason why the first should take so long? Shouldn't I be able to set this variable equal to a select query?
declare@Denominator2 int,
@StartDate datetime,
@EndDate datetime
set @StartDate='2013-02-01'
set @EndDate='2013-09-30'
set @Denominator2=
(
select count(distinct a.MRN)
from [SH-SQL01.BROCKTONHOSPITAL.INTERNAL].DataWarehouse.dbo.formatIDX_PastAppts a
inner join [SH-SQL01.BROCKTONHOSPITAL.INTERNAL].DataWarehouse.dbo.formatIDX_Patients b on
a.MRN=b.MRN
where ApptDt between DATEADD(yy, - 2, @StartDate) and dateadd(d, 1, @EndDate)
and a.STATUS = 'ARR'
and a.MRN <> ''
and a.Deceased <> 'Y'
and b.PCPProvid in ('1790949956','1548245038','1881879591','1871506311')
and b.PrimIns='TUFTS MEDICARE PREF-SMG PCP'
)
select @Denominator2
declare@Denominator2 int,
@StartDate datetime,
@EndDate datetime
set @StartDate='2013-02-01'
set @EndDate='2013-09-30'
select distinct a.MRN
into #Count
from [SH-SQL01.BROCKTONHOSPITAL.INTERNAL].DataWarehouse.dbo.formatIDX_PastAppts a
inner join [SH-SQL01.BROCKTONHOSPITAL.INTERNAL].DataWarehouse.dbo.formatIDX_Patients b on
a.MRN=b.MRN
where ApptDt between DATEADD(yy, - 2, @StartDate) and dateadd(d, 1, @EndDate)
and a.STATUS = 'ARR'
and a.MRN <> ''
and a.Deceased <> 'Y'
and b.PCPProvid in ('1790949956','1548245038','1881879591','1871506311')
and b.PrimIns='TUFTS MEDICARE PREF-SMG PCP'
set @Denominator2=(select count(*) from #Count)
select @Denominator2
October 24, 2013 at 12:13 pm
I can't say for sure without a query plan although I virtually always get much better results breaking a monolithic query into several separate queries and using "procedural" step logic instead of "set oriented" logic (not trolling, just giving empirical data).
I think you can do the date processing beforehand and get better results. Create a variable like @DtMinus2days and use that in the query WHERE clause instead of the calculation.
October 24, 2013 at 12:26 pm
Thanx.
October 24, 2013 at 12:29 pm
I'm pretty sure the differences are in how the query is dealing with the DISTINCT statement. Look at the execution plan to see how that is resolving itself.
Since DISTINCT is an aggregate operator, do you need it? In general, not always, it's an indication of a structure that is inadequately preventing duplication of data or a bad series of joins or join criteria. Maybe a TOP 1 with an ORDER BY assuming you can get an appropriate set of joins will perform better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2013 at 12:31 pm
Thanx. Unfortunately, I don't know how to read execution plans very well.
I will try your suggestions.
October 24, 2013 at 12:33 pm
Check out my sig for a book on reading execution plans. You can even download it for free.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2013 at 12:44 pm
Will do. Thanx.
October 24, 2013 at 12:54 pm
Just a couple of ideas to test for performance, but might not be an improvement.
declare@Denominator2 int,
@StartDate datetime,
@EndDate datetime
set @StartDate='2013-02-01'
set @EndDate='2013-09-30'
select @Denominator2= count(*)
FROM
(
select a.MRN
from [SH-SQL01.BROCKTONHOSPITAL.INTERNAL].DataWarehouse.dbo.formatIDX_PastAppts a
inner join [SH-SQL01.BROCKTONHOSPITAL.INTERNAL].DataWarehouse.dbo.formatIDX_Patients b on
a.MRN=b.MRN
where ApptDt between DATEADD(yy, - 2, @StartDate) and dateadd(d, 1, @EndDate)
and a.STATUS = 'ARR'
and a.MRN <> ''
and a.Deceased <> 'Y'
and b.PCPProvid in ('1790949956','1548245038','1881879591','1871506311')
and b.PrimIns='TUFTS MEDICARE PREF-SMG PCP'
group by a.MRN
) x
select @Denominator2
OR
declare@Denominator2 int,
@StartDate datetime,
@EndDate datetime
set @StartDate='2013-02-01'
set @EndDate='2013-09-30'
select distinct a.MRN
into #Count
from [SH-SQL01.BROCKTONHOSPITAL.INTERNAL].DataWarehouse.dbo.formatIDX_PastAppts a
inner join [SH-SQL01.BROCKTONHOSPITAL.INTERNAL].DataWarehouse.dbo.formatIDX_Patients b on
a.MRN=b.MRN
where ApptDt between DATEADD(yy, - 2, @StartDate) and dateadd(d, 1, @EndDate)
and a.STATUS = 'ARR'
and a.MRN <> ''
and a.Deceased <> 'Y'
and b.PCPProvid in ('1790949956','1548245038','1881879591','1871506311')
and b.PrimIns='TUFTS MEDICARE PREF-SMG PCP'
set @Denominator2=@@ROWCOUNT
select @Denominator2
October 24, 2013 at 1:18 pm
Thanx.
October 24, 2013 at 1:43 pm
This knocked off a several seconds. Thanx.
October 24, 2013 at 4:19 pm
NineIron (10/24/2013)
This knocked off a several seconds. Thanx.
What exactly? Are you sure it wasn't helped by the buffer cache?
October 25, 2013 at 5:33 am
4 seconds. I ran the query a couple of times consecutively and saw no difference in the performance of the query. So, I don't think cache has anything to do with the improvement.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply