Difference in query plan with use of COALESCE

Question

Difference in query plan with use of COALESCE

Viking-204480

SSC Eights!

Points: 970
More actions
February 21, 2007 at 3:17 am

#194342

Hello everyone,
I have the query 1 running on SQL Server 2005 instance, and it has different execution plan from that of query 2. The good thing is that query 2 execution results are quicker than query 1. The only difference between these two queries is that I have used COALESCE in query 2 based on suggestion from one of the google threads.
I am unable to figure why the execution plans are different, even though the column on which the function used has primary key constraint.
Query 1:
SELECT
BPE.BusnPartEmpId
FROM
[dbo].tblBusnPartEmp AS BPE WITH (NOLOCK)
INNER JOIN
[dbo].tblDivBranchBusnPartEmp AS DBBPE WITH (NOLOCK) ON DBBPE.BusnPartEmpId = BPE.BusnPartEmpId
INNER JOIN
[dbo].tblDivBranch AS TDB WITH (NOLOCK) ON TDB.DivBranchId = DBBPE.DivBranchId
WHERE
BPE.EmpTypCd = 7
AND TDB.BusnPartId = 1647
AND TDB.DivNo = '000'
Query 2:
SELECT BPE.BusnPartEmpId
FROM [dbo].tblBusnPartEmp AS BPE WITH (NOLOCK)
INNER JOIN [dbo].tblDivBranchBusnPartEmp AS DBBPE WITH (NOLOCK)
ON BPE.BusnPartEmpId = COALESCE(DBBPE.BusnPartEmpId, DBBPE.BusnPartEmpId)
INNER JOIN [dbo].tblDivBranch AS TDB WITH (NOLOCK)
ON TDB.DivBranchId = DBBPE.DivBranchId
WHERE BPE.EmpTypCd = 7
AND TDB.BusnPartId = 1647
AND TDB.DivNo = '000'
Here is the execution plan for queries
Query 1 execution plan:
4251SELECT BPE.BusnPartEmpId FROM [dbo].tblBusnPartEmp AS BPE WITH (NOLOCK) INNER JOIN [dbo].tblDivBranchBusnPartEmp AS DBBPE WITH (NOLOCK) ON DBBPE.BusnPartEmpId = BPE.BusnPartEmpId INNER JOIN [dbo].tblDivBranch AS TDB WITH (NOLOCK) ON TDB.DivBranchId = DBBPE.DivBranchId WHERE BPE.EmpTypCd = 7 AND TDB.BusnPartId = 1647 AND TDB.DivNo = '000'110NULLNULL
4251 |--Parallelism(Gather Streams)121ParallelismGather Streams
4254 |--Hash Match(Inner Join, HASH[BPE].[BusnPartEmpId])=([DBBPE].[BusnPartEmpId]))132Hash MatchInner Join
605514 |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[BPE].[BusnPartEmpId]))143ParallelismRepartition Streams
605514 | |--Table Scan(OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]), WHERE[EDGE].[dbo].[tblBusnPartEmp].[EmpTypCd] as [BPE].[EmpTypCd]=(7)))154Table ScanTable Scan
11094 |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[DBBPE].[BusnPartEmpId]))163ParallelismRepartition Streams
11094 |--Nested Loops(Inner Join, OUTER REFERENCES[TDB].[DivBranchId], [Expr1013]) OPTIMIZED WITH UNORDERED PREFETCH)176Nested LoopsInner Join
14 |--Filter(WHERE[EDGE].[dbo].[tblDivBranch].[DivNo] as [TDB].[DivNo]=N'000'))1107FilterFilter
6274 | |--Nested Loops(Inner Join, OUTER REFERENCES[Bmk1006], [Expr1012]) WITH UNORDERED PREFETCH)11110Nested LoopsInner Join
6274 | |--Index Seek(OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1] AS [TDB]), SEEK[TDB].[BusnPartId]=(1647)) ORDERED FORWARD)11311Index SeekIndex Seek
627627 | |--RID Lookup(OBJECT[EDGE].[dbo].[tblDivBranch] AS [TDB]), SEEK[Bmk1006]=[Bmk1006]) LOOKUP ORDERED FORWARD)11511RID LookupRID Lookup
11091 |--Index Seek(OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp] AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[EDGE].[dbo].[tblDivBranch].[DivBranchId] as [TDB].[DivBranchId]) ORDERED FORWARD)1227Index SeekIndex Seek
Query 2 execution plan:
4251SELECT BPE.BusnPartEmpId FROM [dbo].tblBusnPartEmp AS BPE WITH (NOLOCK) INNER JOIN [dbo].tblDivBranchBusnPartEmp AS DBBPE WITH (NOLOCK) ON BPE.BusnPartEmpId = COALESCE(DBBPE.BusnPartEmpId, DBBPE.BusnPartEmpId) INNER JOIN [dbo].tblDivBranch AS TDB WITH (NOLOCK) ON TDB.DivBranchId = DBBPE.DivBranchId WHERE BPE.EmpTypCd = 7 AND TDB.BusnPartId = 1647 AND TDB.DivNo = '000'110NULLNULL
4251 |--Parallelism(Gather Streams)121ParallelismGather Streams
4254 |--Filter(WHERE[EDGE].[dbo].[tblBusnPartEmp].[EmpTypCd] as [BPE].[EmpTypCd]=(7)))132FilterFilter
11094 |--Nested Loops(Inner Join, OUTER REFERENCES[Bmk1000], [Expr1021]) WITH UNORDERED PREFETCH)143Nested LoopsInner Join
11094 |--Nested Loops(Inner Join, OUTER REFERENCES[Expr1012], [Expr1020]) OPTIMIZED WITH UNORDERED PREFETCH)164Nested LoopsInner Join
00 | |--Compute Scalar(DEFINE[Expr1012]=CASE WHEN [EDGE].[dbo].[tblDivBranchBusnPartEmp].[BusnPartEmpId] as [DBBPE].[BusnPartEmpId] IS NOT NULL THEN [EDGE].[dbo].[tblDivBranchBusnPartEmp].[BusnPartEmpId] as [DBBPE].[BusnPartEmpId] ELSE [EDGE].[dbo].[tblDivBranchBusnPartEmp].[BusnPartEmpId] as [DBBPE].[BusnPartEmpId] END))196Compute ScalarCompute Scalar
11094 | | |--Nested Loops(Inner Join, OUTER REFERENCES[TDB].[DivBranchId], [Expr1019]) OPTIMIZED WITH UNORDERED PREFETCH)1109Nested LoopsInner Join
14 | | |--Filter(WHERE[EDGE].[dbo].[tblDivBranch].[DivNo] as [TDB].[DivNo]=N'000'))11310FilterFilter
6274 | | | |--Nested Loops(Inner Join, OUTER REFERENCES[Bmk1006], [Expr1018]) WITH UNORDERED PREFETCH)11413Nested LoopsInner Join
6274 | | | |--Index Seek(OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1] AS [TDB]), SEEK[TDB].[BusnPartId]=(1647)) ORDERED FORWARD)11614Index SeekIndex Seek
627627 | | | |--RID Lookup(OBJECT[EDGE].[dbo].[tblDivBranch] AS [TDB]), SEEK[Bmk1006]=[Bmk1006]) LOOKUP ORDERED FORWARD)11814RID LookupRID Lookup
11091 | | |--Index Seek(OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp] AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[EDGE].[dbo].[tblDivBranch].[DivBranchId] as [TDB].[DivBranchId]) ORDERED FORWARD)12510Index SeekIndex Seek
11091109 | |--Index Seek(OBJECT[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS [BPE]), SEEK[BPE].[BusnPartEmpId]=[Expr1012]) ORDERED FORWARD)1296Index SeekIndex Seek
11091109 |--RID Lookup(OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]), SEEK[Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)1314RID LookupRID Lookup
Can somebody help me to understand the difference.
Thanks!
Viking

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

the timings are a red herring unless you are absolutely sure both quereis run with a level playing field, so to speak. What's the io difference between the queries?

could you post a link to the article that suggests a coalesce will improve performance.

the plans are different because your query is different. For such a simple query ( looking at the plans ) I'd say you were lacking useful indexes.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Ken McKelvey SSCoach Points: 18766 More actions · Answer 2

COALESCE seems to have some effect. No one had time to work out what in the following thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=323566#bm325392

A quick look at the query plan suggests that the COALESCE is changing an INNER HASH JOIN to an INNER LOOP join.

Ken McKelvey SSCoach Points: 18766 More actions · Answer 3

Viking,

Does:

SELECT BPE.BusnPartEmpId

FROM dbo.tblBusnPartEmp AS BPE WITH (NOLOCK)

INNER LOOP JOIN dbo.tblDivBranchBusnPartEmp AS DBBPE WITH (NOLOCK)

ON BPE.BusnPartEmpId =DBBPE.BusnPartEmpId

INNER JOIN dbo.tblDivBranch AS TDB WITH (NOLOCK)

ON TDB.DivBranchId = DBBPE.DivBranchId

WHERE BPE.EmpTypCd = 7

AND TDB.BusnPartId = 1647

AND TDB.DivNo = '000'

have the same effect as: