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

  • Question

  • 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  
    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.

    Monday, June 11, 2018 8:33 PM

All replies

  • 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.



    Monday, June 11, 2018 8:50 PM
  • 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.

    Tuesday, June 12, 2018 12:18 PM
  • 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


    UPDATE stgl

    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.

    Thursday, May 21, 2020 10:19 AM
  • Did you try to truncate your table of staging records before processing? That helped for me

    TRUNCATE TABLE [stg].[Product_Leaf]

    Friday, August 21, 2020 3:56 PM