locked
Having problems with data updates on MDS 2016 SP1 RRS feed

  • Question

  • We are using in MDS 2016 with SP1 and currently we have problems with updating the Person entity. This entity has aprox 10 M records and this Entity has 41 attributes and 11 of them are domain based. 

    when trying to make update for 10K records it takes couple hours to finish.

    Is this normal? We have checked our indexes and they should ok, data compression is on for the entity and old batches have been cleared from the history. 

    Where to look next?

    -Esa 

    Monday, January 23, 2017 9:42 AM

Answers

  • No it's was just hanging and doing something. I saw in Activity Monitor that process was executing this (below) step. 

    It seems that We were creating small many small batches which did inserts and updates to the data and maybe somehow table statistics wrong. 

    Workaround was to add Update Statistics for staging prosedure, that helped at least processing the batches.

    INSERT INTO @NewOrphans(ID, Code, ColumnName, ColumnValue)
    
    SELECT stgl.ID, stgl.Code, N'[Match Member]', stgl.[Match Member]
    FROM [stg].[HY_Person_Person_Leaf] stgl
    LEFT JOIN mdm.[tbl_5_95_EN] dm -- First see if the member already exists
    ON stgl.[Match Member] = dm.Code
    AND dm.Status_ID = 1 -- Active
    AND dm.Version_ID = @Version_ID
    LEFT JOIN stg.[HY_Person_Person_Leaf] dm2 -- Self-referencing DBA. See if the domain member is being created in the same batch.
    ON stgl.[Match Member] = dm2.Code
    AND stgl.Batch_ID = dm2.Batch_ID
    AND dm2.ErrorCode = 0
    WHERE stgl.ImportType NOT IN (3/*Delete*/, 4/*Purge*/, 5/*DeleteSetNullToRef*/, 6/*PurgeSetNullToRef*/)
    AND LEN(ISNULL(NULLIF(stgl.[Match Member], @NULLText), N'')) > 0
    AND stgl.Batch_ID = @Batch_ID AND stgl.ImportStatus_ID = 0 -- Default
    AND ISNULL(dm.Code, dm2.Code) IS NULL
    UNION -- not UNION ALL because deduplication is needed
    
    SELECT stgl.ID, stgl.Code, N'[Master]', stgl.[Master]
    FROM [stg].[HY_Person_Person_Leaf] stgl
    LEFT JOIN mdm.[tbl_5_95_EN] dm -- First see if the member already exists
    ON stgl.[Master] = dm.Code
    AND dm.Status_ID = 1 -- Active
    AND dm.Version_ID = @Version_ID
    LEFT JOIN stg.[HY_Person_Person_Leaf] dm2 -- Self-referencing DBA. See if the domain member is being created in the same batch.
    ON stgl.[Master] = dm2.Code
    AND stgl.Batch_ID = dm2.Batch_ID
    AND dm2.ErrorCode = 0
    WHERE stgl.ImportType NOT IN (3/*Delete*/, 4/*Purge*/, 5/*DeleteSetNullToRef*/, 6/*PurgeSetNullToRef*/)
    AND LEN(ISNULL(NULLIF(stgl.[Master], @NULLText), N'')) > 0
    AND stgl.Batch_ID = @Batch_ID AND stgl.ImportStatus_ID = 0 -- Default
    AND ISNULL(dm.Code, dm2.Code) IS NULL

    • Marked as answer by EsaManty Friday, February 3, 2017 1:07 PM
    Thursday, January 26, 2017 7:09 AM

All replies

  • I think, in the case of large volumes:

    indexes antagonize Update-command


    from Moscow with money

    Tuesday, January 24, 2017 8:12 PM
  • Hi Esa,

    can you please explain more about this ? how are you trying to update 10K records ?

    thanks,

    santhosh

    Wednesday, January 25, 2017 9:58 AM
  • Hi Santhosh,

    We are doing golden record matching with Profisee Maestro, which publishes thru staging tables the updates for the records and inserts new "master" records to the Entity. 

    Currently I ran the matching 200k records, most of the batches were processed with in a minute, but last batch has been running for 4 hours now. 

    Wednesday, January 25, 2017 11:07 AM
  • interesting, does it throw an error or is it running for 4 hours without any error ? what does the person entity contains extra information when you compare with your other entities which quickly finished the batch ?
    Thursday, January 26, 2017 3:27 AM
  • No it's was just hanging and doing something. I saw in Activity Monitor that process was executing this (below) step. 

    It seems that We were creating small many small batches which did inserts and updates to the data and maybe somehow table statistics wrong. 

    Workaround was to add Update Statistics for staging prosedure, that helped at least processing the batches.

    INSERT INTO @NewOrphans(ID, Code, ColumnName, ColumnValue)
    
    SELECT stgl.ID, stgl.Code, N'[Match Member]', stgl.[Match Member]
    FROM [stg].[HY_Person_Person_Leaf] stgl
    LEFT JOIN mdm.[tbl_5_95_EN] dm -- First see if the member already exists
    ON stgl.[Match Member] = dm.Code
    AND dm.Status_ID = 1 -- Active
    AND dm.Version_ID = @Version_ID
    LEFT JOIN stg.[HY_Person_Person_Leaf] dm2 -- Self-referencing DBA. See if the domain member is being created in the same batch.
    ON stgl.[Match Member] = dm2.Code
    AND stgl.Batch_ID = dm2.Batch_ID
    AND dm2.ErrorCode = 0
    WHERE stgl.ImportType NOT IN (3/*Delete*/, 4/*Purge*/, 5/*DeleteSetNullToRef*/, 6/*PurgeSetNullToRef*/)
    AND LEN(ISNULL(NULLIF(stgl.[Match Member], @NULLText), N'')) > 0
    AND stgl.Batch_ID = @Batch_ID AND stgl.ImportStatus_ID = 0 -- Default
    AND ISNULL(dm.Code, dm2.Code) IS NULL
    UNION -- not UNION ALL because deduplication is needed
    
    SELECT stgl.ID, stgl.Code, N'[Master]', stgl.[Master]
    FROM [stg].[HY_Person_Person_Leaf] stgl
    LEFT JOIN mdm.[tbl_5_95_EN] dm -- First see if the member already exists
    ON stgl.[Master] = dm.Code
    AND dm.Status_ID = 1 -- Active
    AND dm.Version_ID = @Version_ID
    LEFT JOIN stg.[HY_Person_Person_Leaf] dm2 -- Self-referencing DBA. See if the domain member is being created in the same batch.
    ON stgl.[Master] = dm2.Code
    AND stgl.Batch_ID = dm2.Batch_ID
    AND dm2.ErrorCode = 0
    WHERE stgl.ImportType NOT IN (3/*Delete*/, 4/*Purge*/, 5/*DeleteSetNullToRef*/, 6/*PurgeSetNullToRef*/)
    AND LEN(ISNULL(NULLIF(stgl.[Master], @NULLText), N'')) > 0
    AND stgl.Batch_ID = @Batch_ID AND stgl.ImportStatus_ID = 0 -- Default
    AND ISNULL(dm.Code, dm2.Code) IS NULL

    • Marked as answer by EsaManty Friday, February 3, 2017 1:07 PM
    Thursday, January 26, 2017 7:09 AM