Difference in query plan with use of COALESCE | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Difference in query plan with use of COALESCE

Hello everyone,<br /><br />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.<br /><br />I am unable to figure why the execution plans are different, even though the column on which the function used has primary key constraint.<br /><br />Query 1:<br />SELECT <br /> BPE.BusnPartEmpId <br />FROM <br /> [dbo].tblBusnPartEmp AS BPE WITH (NOLOCK) <br />INNER JOIN <br /> [dbo].tblDivBranchBusnPartEmp AS DBBPE WITH (NOLOCK) ON DBBPE.BusnPartEmpId = BPE.BusnPartEmpId <br />INNER JOIN <br /> [dbo].tblDivBranch AS TDB WITH (NOLOCK) ON TDB.DivBranchId = DBBPE.DivBranchId <br />WHERE <br />BPE.EmpTypCd = 7 <br />AND TDB.BusnPartId = 1647 <br />AND TDB.DivNo = ‘000’ <br /><br />Query 2:<br />SELECT BPE.BusnPartEmpId <br /> FROM [dbo].tblBusnPartEmp AS BPE WITH (NOLOCK) <br />INNER JOIN [dbo].tblDivBranchBusnPartEmp AS DBBPE WITH (NOLOCK) <br />ON BPE.BusnPartEmpId = COALESCE(DBBPE.BusnPartEmpId, DBBPE.BusnPartEmpId)<br /> INNER JOIN [dbo].tblDivBranch AS TDB WITH (NOLOCK) <br />ON TDB.DivBranchId = DBBPE.DivBranchId <br /> WHERE BPE.EmpTypCd = 7 <br /> AND TDB.BusnPartId = 1647 <br /> AND TDB.DivNo = ‘000’<br /><br /><br />Here is the execution plan for queries<br />Query 1 execution plan:<br />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<br />4251 |–Parallelism(Gather Streams)121ParallelismGather Streams<br />4254 |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[BPE].[BusnPartEmpId])=([DBBPE].[BusnPartEmpId]))132Hash MatchInner Join<br />605514 |–Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[BPE].[BusnPartEmpId]))143ParallelismRepartition Streams<br />605514 | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblBusnPartEmp].[EmpTypCd] as [BPE].[EmpTypCd]=(7)))154Table ScanTable Scan<br />11094 |–Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[DBBPE].[BusnPartEmpId]))163ParallelismRepartition Streams<br />11094 |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[TDB].[DivBranchId], [Expr1013]) OPTIMIZED WITH UNORDERED PREFETCH)176Nested LoopsInner Join<br />14 |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblDivBranch].[DivNo] as [TDB].[DivNo]=N’000′))1107FilterFilter<br />6274 | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Bmk1006], [Expr1012]) WITH UNORDERED PREFETCH)11110Nested LoopsInner Join<br />6274 | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1] AS [TDB]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[TDB].[BusnPartId]=(1647)) ORDERED FORWARD)11311Index SeekIndex Seek<br />627627 | |–RID Lookup(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblDivBranch] AS [TDB]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Bmk1006]=[Bmk1006]) LOOKUP ORDERED FORWARD)11511RID LookupRID Lookup<br />11091 |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp] AS [DBBPE]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[DBBPE].[DivBranchId]=[EDGE].[dbo].[tblDivBranch].[DivBranchId] as [TDB].[DivBranchId]) ORDERED FORWARD)1227Index SeekIndex Seek<br /><br />Query 2 execution plan:<br />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<br />4251 |–Parallelism(Gather Streams)121ParallelismGather Streams<br />4254 |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblBusnPartEmp].[EmpTypCd] as [BPE].[EmpTypCd]=(7)))132FilterFilter<br />11094 |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Bmk1000], [Expr1021]) WITH UNORDERED PREFETCH)143Nested LoopsInner Join<br />11094 |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1012], [Expr1020]) OPTIMIZED WITH UNORDERED PREFETCH)164Nested LoopsInner Join<br />00 | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[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<br />11094 | | |–Nested Loops(Inner Join, OUTER REFER ENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[TDB].[DivBranchId], [Expr1019]) OPTIMIZED WITH UNORDERED PREFETCH)1109Nested LoopsInner Join<br />14 | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblDivBranch].[DivNo] as [TDB].[DivNo]=N’000′))11310FilterFilter<br />6274 | | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Bmk1006], [Expr1018]) WITH UNORDERED PREFETCH)11413Nested LoopsInner Join<br />6274 | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1] AS [TDB]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[TDB].[BusnPartId]=(1647)) ORDERED FORWARD)11614Index SeekIndex Seek<br />627627 | | | |–RID Lookup(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblDivBranch] AS [TDB]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Bmk1006]=[Bmk1006]) LOOKUP ORDERED FORWARD)11814RID LookupRID Lookup<br />11091 | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp] AS [DBBPE]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[DBBPE].[DivBranchId]=[EDGE].[dbo].[tblDivBranch].[DivBranchId] as [TDB].[DivBranchId]) ORDERED FORWARD)12510Index SeekIndex Seek<br />11091109 | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS [BPE]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[BPE].[BusnPartEmpId]=[Expr1012]) ORDERED FORWARD)1296Index SeekIndex Seek<br />11091109 |–RID Lookup(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)1314RID LookupRID Lookup<br /><br /><br />Can somebody help me to understand the difference.<br /><br />RoyalSher.<br />*********<br />The world is the great gymnasium where we come to make ourselves strong.
i am not sure why the COALESCE was suggested in something like this
unless you want to fool the optimizer
in which case it did in any case, the execution plan is this format is nearly impossible to read
use the save plan as xml optiin that i described in the 2nd to top post on queries
]]>