SQLTeam.com | Weblogs | Forums

Alternative to Subquery in join

Hi, as part of optimization I'm trying to find a best way to replace subquery used in join , is there any good way to do it.

DECLARE @curdate datetime
SET @curdate=getdate()
SELECT distinct SIS.StudentID,StudentCoverage.StudentCoverageDataID, Student.FirstName,Student.LastName,
Student.DateOfBirth,Student.Gender,ASMT.AssessmentDate
FROM SIS (nolock)
inner join SISMaster (nolock) on SISMaster.SISID = SIS.SISID
inner join StudentCoverage (nolock) on StudentCoverage.StudentID = SIS.StudentID
inner join Organization (nolock) on StudentCoverage.OrgID = Organization.OrganizationID
inner join Student (nolock) on Student.StudentID = SIS.StudentID
inner join StudentCoverageData (nolock) on StudentCoverageData.StudentCoverageID = StudentCoverage.StudentCoverageID
AND StudentCoverageData.StudentCoverageDataID =
(select top 1 StudentCoverageData.StudentCoverageDataID
from StudentCoverage inner join StudentCoverageData on StudentCoverageData.StudentCoverageID = StudentCoverage.StudentCoverageID
where StudentCoverage.StudentId = SIS.StudentID
AND StudentCoverageData.Active = 1 AND StudentCoverageData.EffectiveDate <= @curdate
AND (StudentCoverageData.ExitDate is null or StudentCoverageData.ExitDate > @curdate)
order by StudentCoverageData.AsOfDate desc)

I would use ROW_NUMBER() OVER() either in CTE of as a subquery-JOIN

Get rid of those NOLOCKS - are you aware that your query will, on occasion, miss some rows completely, or include some rows twice?

Hi Kristen,

Could you please explain how does NOLOCKS miss some rows completely, or include some rows twice? I'm new to Sql Server.

thanks

If you want sensible answers you need to post sensible questions.
There is no table or alias named ASMT in your query.

As for NOLOCK:

https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/

etc

2 Likes

In the absence of further detail you might like to start with the following and refine it.

DECLARE @curdate datetime = CURRENT_TIMESTAMP;

WITH SCAD
AS
(
	SELECT C1.studentid, D1.studentcoveragedataid
		--,D1.asofdate
		--,C1.orgid
		,ROW_NUMBER() OVER (PARTITION BY C1.studentid ORDER BY D1.asofdate DESC) AS rn
	FROM studentcoveragedata D1
		JOIN studentcoverage C1
			ON D1.studentcoverageid = C1.studentcoverageid
	WHERE D1.active = 1
		AND D1.effectivedate <= @curdate
		AND (D.exitdate IS NULL OR D.exitdate > @curdate)
)
--select * from SCAD
SELECT --DISTINCT
	S.studentid
	,D.studentcoveragedataid
	,S.firstname
	,S.lastname
	,S.dateofbirth
	,S.gender
	--,asmt.assessmentdate
FROM student S
	JOIN SCAD D
		ON I.studentid = D.studentid
			AND D.rn = 1
	--JOIN sis I
	--	ON S.studentid = I.studentid
	--JOIN sismaster M
	--	ON I.sisid = M.sisid
	--JOIN organization O
	--	ON D.orgid = O.organizationid
;

That's good to hear. Most of the time NOLOCK crops up in discussion here the O/p doesn't want to be told its a problem and replies with "It's company policy" - which scares the pants off me!