Question

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  

SELECT Code
into #Duplicated_Codes
FROM [stg].[Product_Leaf]
WHERE BatchTag = @BatchTag
AND ImportStatus_ID = 0 -- Default  
GROUP BY Code
HAVING COUNT(1) > 1

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

DROP TABLE #Duplicated_Codes


END; -- IF  
ELSE IF @Batch_ID IS NOT NULL BEGIN  
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.


Sponsored



Replies

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.

HTH,

Eric

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.