Query Performance - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Performance

Author

Topic

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-08-21 : 09:46:55

I am getting a longer query time from this portion:

AND pv.PatientProfileId NOT IN (
SELECT OwnerId 
	FROM [WMG-MILLBROOK].PracticeManager.dbo.Appointments
	WHERE ApptStatusMId IN ('305','10326') AND ApptKind = '1' AND ApptTypeId IN (
		SELECT ApptTypeId 
		FROM [WMG-MILLBROOK].PracticeManager.dbo.ApptType 
		WHERE Name LIKE '%Physical%')
	AND ApptStart >= getdate() AND ApptStart <= dateadd(yy,1,getdate())
	)

Is there any way to not join instead of using NOT IN?


SET NOCOUNT ON
BEGIN TRAN

/*Physical Exam Recalls - AGE 50-64 (No-Email)*/
DECLARE c_PhysicalExamRecall CURSOR STATIC FORWARD_ONLY FOR
SELECT 	pv.PatientProfileId,
	pv.DoctorId,
	max(pv.visit) as MaxVisit, 
	dateadd(yy,1,max(pv.visit)) as RecallDate
FROM [WMG-MILLBROOK].PracticeManager.dbo.PatientVisitProcs pvp
INNER JOIN [WMG-MILLBROOK].PracticeManager.dbo.PatientVisit pv on pv.PatientVisitId = pvp.PatientVisitId
INNER JOIN [WMG-MILLBROOK].PracticeManager.dbo.PatientProfile pp on pp.PatientProfileId = pv.PatientProfileId
WHERE pvp.cptcode 
IN ('99395','99396','99397','99385','99386','99387')
AND pv.DoctorId in (
SELECT DoctorFacilityId FROM [WMG-MILLBROOK].PracticeManager.dbo.DoctorFacility 
	WHERE SpecialtyMId = 224) /*Internal Medicine - 224*/
AND pv.PatientProfileId NOT IN (
SELECT OwnerId 
	FROM [WMG-MILLBROOK].PracticeManager.dbo.Appointments
	WHERE ApptStatusMId IN ('305','10326') AND ApptKind = '1' AND ApptTypeId IN (
		SELECT ApptTypeId 
		FROM [WMG-MILLBROOK].PracticeManager.dbo.ApptType 
		WHERE Name LIKE '%Physical%')
	AND ApptStart >= getdate() AND ApptStart <= dateadd(yy,1,getdate())
	)
GROUP BY pv.PatientProfileId, pp.birthdate, pv.DoctorId
HAVING month(dateadd(yy,1,max(pv.visit))) = month(dateadd(mm,1,getdate()))
AND year(dateadd(yy,1,max(pv.visit))) = year(getdate())
AND (CASE
	WHEN dateadd(year, datediff (year, pp.birthdate, getdate()), pp.birthdate) > getdate()
	THEN datediff (year, pp.birthdate, getdate()) - 1
	ELSE datediff (year, pp.birthdate, getdate())
	END) BETWEEN 50 AND 64
ORDER BY pv.PatientProfileId

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 10:19:45

1) NOT EXISTS
2) LEFT JOIN

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 10:33:55

[code]SELECT pv.PatientProfileId,
pv.DoctorId,
max(pv.visit) as MaxVisit,
dateadd(year, 1, max(pv.visit)) as RecallDate
FROM [WMG-MILLBROOK].PracticeManager.dbo.PatientVisitProcs as pvp
INNER JOIN [WMG-MILLBROOK].PracticeManager.dbo.PatientVisit as pv on pv.PatientVisitId = pvp.PatientVisitId
INNER JOIN [WMG-MILLBROOK].PracticeManager.dbo.PatientProfile as pp on pp.PatientProfileId = pv.PatientProfileId
INNER JOIN [WMG-MILLBROOK].PracticeManager.dbo.DoctorFacility AS df ON df.DoctorFacilityId = pv.DoctorId
LEFT JOIN (
SELECT a.OwnerId
FROM [WMG-MILLBROOK].PracticeManager.dbo.Appointments AS a
INNER JOIN [WMG-MILLBROOK].PracticeManager.dbo.ApptType AS at ON at.ApptTypeId = a.ApptTypeId
WHERE a.ApptStatusMId IN ('305', '10326')
AND a.ApptKind = '1'
AND at.Name LIKE '%Physical%'
AND ApptStart >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
AND ApptStart < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 1)
) AS x ON x.OwnerId = pv.PatientProfileId
WHERE pvp.cptcode IN ('99395', '99396', '99397', '99385', '99386', '99387')
AND df.SpecialtyMId = 224
AND x.OwnerId IS NULL
GROUP BY pv.PatientProfileId,
pp.birthdate,
pv.DoctorId
HAVING dateadd(year, 1, max(pv.visit)) >= DATEADD(MONTH, DATEDIFF(MONTH, -1, CURRENT_TIMESTAMP), 0)
AND dateadd(year, 1, max(pv.visit)) < DATEADD(MONTH, DATEDIFF(MONTH, -32, CURRENT_TIMESTAMP), 0)
AND CASE
WHEN DATEPART(DAY, pp.birthdate) > DATEPART(DAY, CURRENT_TIMESTAMP) THEN DATEDIFF(MONTH, pp.birthdate, CURRENT_TIMESTAMP) - 1
ELSE DATEDIFF(MONTH, pp.birthdate, CURRENT_TIMESTAMP)
END / 12 BETWEEN 50 and 64
ORDER BY pv.PatientProfileId[/code]

E 12°55'05.25"
N 56°04'39.16"

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-08-21 : 11:31:43

There is no performance gain since you are using a LEFT JOIN, it still has to go through the same amount of records.

I was thinking of something like:


AND pv.PatientProfileId NOT IN (
SELECT OwnerId 
	FROM [WMG-MILLBROOK].PracticeManager.dbo.Appointments
	WHERE ApptStatusMId IN ('305','10326') AND ApptKind = '1' AND ApptTypeId IN (
		SELECT ApptTypeId 
		FROM [WMG-MILLBROOK].PracticeManager.dbo.ApptType 
		WHERE Name LIKE '%Physical%')
	AND ApptStart >= getdate() AND ApptStart <= dateadd(yy,1,getdate())
	AND OwnerID IN (SELECT PatientProfileId FROM PV) )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 12:39:36

What did SQL Profiler tell you?
What CPU, Duration and Reads are there between the two queries?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources