SQL Server Performance Forum – Threads Archive

More issues during development

Hello everybody, I have a query which updates the target table from a staging area. Query: UPDATE [EDGE].[dbo].[tblBusnPartEmp] SET EmpDeceasedIndCd= CASE WHEN B.EmpDynastyCd=1 THEN 1 ELSE 2 END ,EmpDynastyCd= CASE WHEN B.EmpDynastyCd=1 THEN 0 ELSE B.EmpDynastyCd END ,UpdateDt=GetDate() ,UpdateId=1 ,AppId=’OD2-Cleanup' ,FunctionId=60606 FROM [EDGE_Staging].[dbo].[Edge_CusttblBusnPartEmpDynastyCd] B INNER JOIN [EDGE].[dbo].[tblBusnPartEmp] A ON A.BusnPartEmpId = B.BusnPartEmpId WHERE B.ConvStatus=0 Execution Plan: |–Table Update(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[EDGE].[dbo].[tblBusnPartEmp]), SET<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tblBusnPartEmp].[EmpDeceasedIndCd]=RaiseIfNull([Expr1004]), [tblBusnPartEmp].[FunctionId]=RaiseIfNull(60606), [tblBusnPartEmp].[UpdateDt]=RaiseIfNull(getdate()), [tblBusnPartEmp].[UpdateId]=Ra 10 2 1 Table Update Update OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[EDGE].[dbo].[tblBusnPartEmp]), SET<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tblBusnPartEmp].[EmpDeceasedIndCd]=RaiseIfNull([Expr1004]), [tblBusnPartEmp].[FunctionId]=RaiseIfNull(60606), [tblBusnPartEmp].[UpdateDt]=RaiseIfNull(getdate()), [tblBusnPartEmp].[UpdateId]=RaiseIfNull(1), [tbl NULL 749539.94 1.0754779E-2 0.74953991 21 48.861309 NULL NULL PLAN_ROW 0 1.0 |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1004]=If (.[EmpDynastyCd]=1) then 1 else 2, [Expr1005]=If (.[EmpDynastyCd]=1) then 0 else .[EmpDynastyCd], [Expr1016]=getdate())) 10 3 2 Compute Scalar Compute Scalar DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1004]=If (.[EmpDynastyCd]=1) then 1 else 2, [Expr1005]=If (.[EmpDynastyCd]=1) then 0 else .[EmpDynastyCd], [Expr1016]=getdate()) [Expr1004]=If (.[EmpDynastyCd]=1) then 1 else 2, [Expr1005]=If (.[EmpDynastyCd]=1) then 0 else .[EmpDynastyCd], [Expr1016]=getdate() 749539.94 0.0 7.4953996E-2 91 48.101013 [Bmk1000], [Expr1004], [Expr1005], [Expr1016], [Expr1007], [Expr1008], [Expr1009] NULL PLAN_ROW 0 1.0 |–Top(ROWCOUNT est 0) 10 4 3 Top Top NULL NULL 749539.94 0.0 7.4953996E-2 19 48.026062 [Bmk1000], .[EmpDynastyCd] NULL PLAN_ROW 0 1.0 |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]=[Bmk1000]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />.[EmpDynastyCd]=ANY(.[EmpDynastyCd]))) 10 5 4 Hash Match Aggregate HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]=[Bmk1000]) .[EmpDynastyCd]=ANY(.[EmpDynastyCd]) 749539.94 15.852559 19.073994 19 47.951107 [Bmk1000], .[EmpDynastyCd] NULL PLAN_ROW 0 1.0 |–Merge Join(Inner Join, MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[BusnPartEmpId])=(.[BusnPartEmpId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />.[BusnPartEmpId]=[A].[BusnPartEmpId])) 10 6 5 Merge Join Inner Join MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[BusnPartEmpId])=(.[BusnPartEmpId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />.[BusnPartEmpId]=[A].[BusnPartEmpId]) NULL 749539.94 0.0 4.6337261 27 13.024555 [Bmk1000], .[EmpDynastyCd] NULL PLAN_ROW 0 1.0 |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS [A]), ORDERED FORWARD) 10 7 6 Index Scan Index Scan OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS [A]), ORDERED FORWARD [Bmk1000], [A].[BusnPartEmpId] 1114430.0 1.5805415 1.2259516 19 2.806493 [Bmk1000], [A].[BusnPartEmpId] NULL PLAN_ROW 0 1.0 |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Edge_Staging].[dbo].[Edge_CusttblBusnPartEmpDynastyCd].[IX_Edge_CusttblBusnPartEmpDynastyCd] AS ), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />.[ConvStatus]=0) ORDERED FORWARD) 10 8 6 Clustered Index Seek Clustered Index Seek OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Edge_Staging].[dbo].[Edge_CusttblBusnPartEmpDynastyCd].[IX_Edge_CusttblBusnPartEmpDynastyCd] AS ), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />.[ConvStatus]=0) ORDERED FORWARD .[BusnPartEmpId], .[EmpDynastyCd] 957792.0 4.5278101 1.0565231 42 5.5843329 .[BusnPartEmpId], .[EmpDynastyCd] NULL PLAN_ROW 0 1.0 I tried tweaking with execution plan as best possible, but still I have issues running the query to its end. I am unable to avoid hash join, which is the one taking 71% of query execution time. Please help if this query could be rewritten or any advises is a welcome for me. I have been struggling with this for past 2 days. I can’t add any indexes to the target tables, the only playing could be done on table in staging area. TIA. RoyalSher. ********* The world is the great gymnasium where we come to make ourselves strong.

Your query looks fine to me. So I don’t think a rewrite would make it more efficient. Not sure if this also works with UPDATEs, but you can specify a JOIN hint like INNER LOOP JOIN to force a certain JOIN type. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Thanks for the quick look thro’ Frank. I tried with the option you have given, and still its running for nearly half hour. I suspect a Hash Join in the query which is taking 71% of time based on execution plan. Is there any other way for me to change the query so that Hash Join is not used. RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

]]>