I am looking through a Stored Procedure that duplicates existing source records.
It is a self join and unfortunate the source records does not have logical relationships to the destination record; as the relationship is defined by the user and it is malleable.
Here is the original query
update [dbo].Student_BTWT set paidhours=( select paidhours from Student_BTWT where studentID=@tmpstudentID ) where studentID=@studentID
Revised Query – Inner Join
update tblBTWT set paidhours= tblBTWTParent.paidHours from [dbo].Student_BTWT tblBTWT inner join [dbo].Student_BTWT tblBTWTParent on tblBTWTParent.studentID = @tmpstudentID where tblBTWT.studentID = @studentID
Revised – Cross Apply /Windowing Function
update tblBTWT set paidhours= tblBTWTParent.paidHours from [dbo].Student_BTWT tblBTWT cross apply ( select tblBTWTParent.* , rn = ROW_NUMBER() OVER(ORDER BY [refID] DESC) from [dbo].Student_BTWT tblBTWTParent where tblBTWTParent.studentID = @tmpstudentID ) tblBTWTParent where tblBTWT.studentID = @studentID and tblBTWTParent.rn=1
The original query uses a worktable to store values from the subselect as well as a Table Spool, unfortunately an “Eager Spool“.
Our corrected options does not need worktables nor spools.
But, we are notified that we do not have real joins by the presence of the “No Join Predicate” warning.
- Alexander Kuznetsov
- Using CROSS APPLY to optimize joins on BETWEEN conditions
- When should I use Cross Apply over Inner Join?