Alternative to Subquery in join

Transact-SQL
, , ,
1

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)

2

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?

3

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

4

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:

etc

2 Likes
5

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
;
6

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!