Monday, October 3, 2011

T-SQL Tuesday 23: Nested Joins

You don't see it mentioned very often, but it's one of my favorite tuning tricks - the nested join.  There's nothing groundbreaking here, just my modest contribution to T-SQL Tuesday.

Allow me to present to you an actual query that surfaced on my production server today.  On the surface, this looks like a query that could be stripped down and highly tuned.  The complication is the CandidateBiometricIdentityWorkStatus table - there can be multiple rows in this table for a given OrderItemID, and we don't want to return ANY OrderItemID values that have a status of 'NEW', 'IN_PROCESS', or 'ERROR'.  In a classic case of non-set-based thinking, the developer produced this query:

UPDATE dbo.ResultUploadCompleteQueue
SET    RequestMonitorID = 12
WHERE  RequestMonitorID IS NULL
	   AND OrderItemID IN
	   	(
	   	    -- Find all OrderItemID in the work table that are ready for continued results processing
			SELECT DISTINCT work1.OrderItemID
			FROM dbo.CandidateBiometricIdentityWork work1
			INNER JOIN dbo.ResultUploadCompleteQueue resultQ
				ON work1.OrderItemID = resultQ.OrderItemID
			INNER JOIN dbo.CandidateBiometricIdentityWorkType AS workType
				ON work1.CandidateBiometricIdentityWorkTypeID = workType.CandidateBiometricIdentityWorkTypeID
			INNER JOIN dbo.CandidateBiometricIdentityRequestType AS requestType
				ON work1.CandidateBiometricIdentityRequestTypeID = requestType.CandidateBiometricIdentityRequestTypeID
			WHERE workType.WorkType = 'IDENTIFY' 
				AND requestType.RequestType = 'BATCH'
				AND work1.InResultsProcessing = 1
			     AND NOT EXISTS
				(
				    -- Find all work for the OrderItemID that is not complete and exclude from results
					SELECT 1
					FROM dbo.CandidateBiometricIdentityWork AS work2
					INNER JOIN dbo.CandidateBiometricIdentityWorkType AS workType
						ON work2.CandidateBiometricIdentityWorkTypeID = workType.CandidateBiometricIdentityWorkTypeID
					INNER JOIN dbo.CandidateBiometricIdentityRequestType AS requestType
						ON work2.CandidateBiometricIdentityRequestTypeID = requestType.CandidateBiometricIdentityRequestTypeID
					INNER JOIN dbo.CandidateBiometricIdentityWorkStatus AS workStatus
						ON work2.CandidateBiometricIdentityWorkStatusID = workStatus.CandidateBiometricIdentityWorkStatusID
					WHERE work2.OrderItemID = work1.OrderItemID
						  AND workType.WorkType = 'IDENTIFY' 
						  AND requestType.RequestType = 'BATCH'
						  AND work2.InResultsProcessing = 1
						  AND workStatus.status IN ('NEW','IN_PROCESS','ERROR')
				)
	   	)

This is the estimated plan for the query.  Hard to follow, inefficient (Eager Spool, anyone), can be improved.



Not only can the UPDATE be improved with a join, but the NOT IN filter can be replaced with, my favorite, a nested join.  I rewrote the query, introduced the developer to nested joins (something he'd never seen before), and produced a much cleaner, much more efficient query plan.

UPDATE ResultUploadCompleteQueue
SET RequestMonitorID = 12
FROM dbo.CandidateBiometricIdentityWork
INNER JOIN dbo.ResultUploadCompleteQueue
	ON CandidateBiometricIdentityWork.OrderItemID = ResultUploadCompleteQueue.OrderItemID
INNER JOIN dbo.CandidateBiometricIdentityWorkType
	ON CandidateBiometricIdentityWork.CandidateBiometricIdentityWorkTypeID = CandidateBiometricIdentityWorkType.CandidateBiometricIdentityWorkTypeID
INNER JOIN dbo.CandidateBiometricIdentityRequestType
	ON CandidateBiometricIdentityWork.CandidateBiometricIdentityRequestTypeID = CandidateBiometricIdentityRequestType.CandidateBiometricIdentityRequestTypeID
LEFT JOIN dbo.CandidateBiometricIdentityWork AS CandidateBiometricIdentityWorkPending
	INNER JOIN dbo.CandidateBiometricIdentityWorkType AS CandidateBiometricIdentityWorkTypePending
		ON CandidateBiometricIdentityWorkPending.CandidateBiometricIdentityWorkTypeID = CandidateBiometricIdentityWorkTypePending.CandidateBiometricIdentityWorkTypeID
	INNER JOIN dbo.CandidateBiometricIdentityRequestType AS CandidateBiometricIdentityRequestTypePending
		ON CandidateBiometricIdentityWorkPending.CandidateBiometricIdentityRequestTypeID = CandidateBiometricIdentityRequestTypePending.CandidateBiometricIdentityRequestTypeID
	INNER JOIN dbo.CandidateBiometricIdentityWorkStatus AS CandidateBiometricIdentityWorkStatusPending
		ON CandidateBiometricIdentityWorkPending.CandidateBiometricIdentityWorkStatusID = CandidateBiometricIdentityWorkStatusPending.CandidateBiometricIdentityWorkStatusID
	ON CandidateBiometricIdentityWork.OrderItemID = CandidateBiometricIdentityWorkPending.OrderItemID
		AND CandidateBiometricIdentityWorkPending.InResultsProcessing = 1
		AND CandidateBiometricIdentityWorkTypePending.WorkType = 'IDENTIFY'
		AND CandidateBiometricIdentityRequestTypePending.RequestType = 'BATCH'
		AND CandidateBiometricIdentityWorkStatusPending.Status IN ('NEW','IN_PROCESS','ERROR')
WHERE CandidateBiometricIdentityWorkType.WorkType = 'IDENTIFY' 
	AND CandidateBiometricIdentityRequestType.RequestType = 'BATCH'
	AND CandidateBiometricIdentityWork.InResultsProcessing = 1
	AND ResultUploadCompleteQueue.RequestMonitorID IS NULL
	AND CandidateBiometricIdentityWorkPending.OrderItemID IS NULL


0 comments:

Post a Comment