Execution of staging procedure (stg.udp_entity_leaf) isn't finishing.

Category: sql server masterdata


Cdias.mcm on Mon, 11 Jun 2018 20:33:59

Hi all,

I'm executing a staging procedure to process data that I've just inserted into staging table. Every time I tryed to execute the procedure, the process is "freezing" on update below:

 UPDATE stgl   SET ErrorCode = stgl.ErrorCode | 2   FROM [stg].[Product_Leaf] stgl   INNER JOIN [stg].[Product_Leaf] stgl2   ON      stgl.Code = stgl2.Code       AND stgl.ID <> stgl2.ID       AND stgl.Batch_ID = stgl2.Batch_ID   WHERE stgl.ImportStatus_ID = 0 -- Default       AND stgl.Batch_ID = @Batch_

I checked the code and created a solution that is running very fast:

IF LEN(@BatchTag) > 0 BEGIN  

into #Duplicated_Codes
FROM [stg].[Product_Leaf]
WHERE BatchTag = @BatchTag
AND ImportStatus_ID = 0 -- Default  

UPDATE [stg].[Product_Leaf]
SET ErrorCode = ErrorCode | 2 
FROM #Duplicated_Codes)
AND BatchTag = @BatchTag
AND ImportStatus_ID = 0 -- Default 

DROP TABLE #Duplicated_Codes

END; -- IF  
UPDATE stgl  
SET ErrorCode = stgl.ErrorCode | 2  
FROM [stg].[Product_Leaf] stgl  
INNER JOIN [stg].[Product_Leaf] stgl2
ON      stgl.Code = stgl2.Code  
AND stgl.ID <> stgl2.ID  
AND stgl.Batch_ID = stgl2.Batch_ID  
WHERE stgl.ImportStatus_ID = 0 -- Default  
AND stgl.Batch_ID = @Batch_ID; 
END; -- IF  

The problem is, every time we changed the structure on MDS entity the MDS recreates its correlated procedure (stg.udp_entity_leaf) and we lost the update that I did into the procedure.

My questions are,

There is any kind of path to solve this issue?

Am I doing something wrong? I mean, does anyone has this same issue?

I'm using the MDS for SQL Server 2016.


EricMelcher on Mon, 11 Jun 2018 20:50:45

After looking at this briefly, a couple of thoughts.

Editing the stored procedure or creating your own derivative is definitely not a good approach. As you mention, the staging sproc is re-generated every time you make a change to the entity definition. You're better off finding and fixing the root cause.

In this case, the line that is hanging looks to be updating the error code information on the staging table, which means that you likely have some sort of error in the data you're staging, and the logging of that issue is taking a long time. Again, no guarantee this is right, but that's where I'd start troubleshooting. Oftentimes this is due to trying to load a lot of domain based attributes with invalid values, or bad dates or numbers, etc.

Begin by staging the data, and only populating the  Code attribute, and see if that completes. Then keep adding more attributes into the staging load until you uncover the issue.



Cdias.mcm on Tue, 12 Jun 2018 12:18:08

Hi Eric, tks for your reply,

However, this update verify if there is any duplicated record in staging table by code, I waited 12 hours and the results of execution was: There isn't any duplicated records, as the results of "my" query.

Kulikowski_oficjalnie on Thu, 21 May 2020 10:19:54

We have the same problem

stg.udp_entity_leaf) isn't finishing

 and we have changed procedure in this way which also works fast: 

;with src as


select Code , batch_id, count(*) cnt

from mds.[stg].[Produkty_Leaf]

group by Code , batch_id

having count(*) > 1



SET stgl.ErrorCode = stgl.ErrorCode | 2

FROM [stg].[Produkty_Leaf] stgl

INNER JOIN src stgl2

ON      stgl.Code = stgl2.Code

    AND stgl.Batch_ID = stgl2.Batch_ID

WHERE stgl.ImportStatus_ID = 0 -- Default

    AND stgl.Batch_ID = @Batch_ID

We also have problem with overwriting MDS procedures when we change the structure on MDS entity.

Kevin SQL on Fri, 21 Aug 2020 15:56:00

Did you try to truncate your table of staging records before processing? That helped for me

TRUNCATE TABLE [stg].[Product_Leaf]