Transact – Avoid WorkTable in DisJointed Joins

Background

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.

Query

Original Query

Here is the original query

Code


	update [dbo].Student_BTWT 
	set 
	       paidhours=(
                            select paidhours 
                            from Student_BTWT 
                            where studentID=@tmpstudentID
                         )

	where studentID=@studentID

Query Plan

QueryPlan


Revised Query – Inner Join

Code


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

Query Plan

QueryPlanInnerJoin

Revised – Cross Apply /Windowing Function

Code


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


Query Plan

CrossApplyWindowingFunction

Statistics I/O

Picture

StatisticsIO

 

Tabulated

 

 

Query Table Scan Count Logical Reads
Original Query Actual Table  2  9
 Worktable  1  5
Inner Join Actual Table  2  9
 Worktable
Cross Apply Actual Table  2  9
 Worktable

Summary

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.

 

References

  1. Using CROSS APPLY to optimize joins on BETWEEN conditions
    http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/07/07/using-cross-apply-to-optimize-joins-on-between-conditions.aspx

  2. When should I use Cross Apply over Inner Join?
    http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s