locked
MDS 2012 Delete not working RRS feed

  • Question

  • Hi,

    I am trying to delete MDS 2012 records. I loaded the records in the stg_Leaf table after disabling the auto-generated code (which was originally enabled) to set the code with the same values as the ones originally inserted. My understanding is that the code needs to match for delete (importType=6) to work based on what I've read so far. However, the staging process seems to be stuck with a running status. When I used sql profiler to check what's going on, I found that it's going through a while loop over and over. When I ran the query it used to create the temp table in the management studio, I didn't get any results. So, I am not sure why it's running in the while loop for so long (couple of days and I had to terminate the process because it was using 100% of CPU).

    Please advise. Let me know if additional details are required.

    Thanks & Regards,

    Kamal


    Monday, February 11, 2013 2:43 PM

Answers

  • I opened a ticket with Microsoft support. They discovered a bug in the staging stored procedure which was missing an index on one of the temp tables (#). The fix they provided was to add a line after the CREATE table statement for temp TableRR to create an index on the StagingID field on TableRR. Here is the line that fixed the issue.

    CREATE CLUSTERED INDEX TableRR_IX1 on #TableRR(StagingId)

    Before adding this line, it took about 12 hours to delete one million records. After adding this line, the same number of records were deleted in half an hour. The MDS product version that I am working with is 11.0.2218.0 and the MDS database version is 11.0.2.1.

    Thanks & Regards,

    Kamal

    • Marked as answer by KamalShaikh Thursday, February 14, 2013 9:45 PM
    Thursday, February 14, 2013 9:45 PM

All replies

  • Hi Kamal, 

    I still have no clues for solving your issue, but as a workaround you may try to use API :

    (i was using this code to delete all members from one entity  in my mdsmanager tool on 2008R2, but I don't know if this part of my code is still working on 2012, although there is no compilation error [I did not test it yet on SQL 2012])

      this.BulkDeleteMembersFromEntity((CustomEntity)this.ucManageEntities1.lstEntities.SelectedItem);
     public void BulkDeleteMembersFromEntity(CustomEntity selectedEntity)
            {
                try
                {
                    Cursor.Current = Cursors.WaitCursor;
                    if (this.lstModels.SelectedItem == null || this.lstVersions.SelectedItem == null)
                        return;
                    string name1 = selectedEntity.Name;
                    Identifier identifier1 = (Identifier)selectedEntity.entityId;
                    Identifier modelId = this.lstModels.SelectedItem as Identifier;
                    string name2 = (this.lstModels.SelectedItem as Identifier).Name;
                    Identifier identifier2 = (this.lstVersions.SelectedItem as CustomVersion).Identifier;
                    string name3 = (this.lstVersions.SelectedItem as CustomVersion).Name;
                    Collection<Common.ServiceReference1.Member> members = MDSWrapper.GetMembers(modelId, identifier2, selectedEntity, "");
                    OperationResult or = new OperationResult();
                    Collection<EntityMembers> modelMembers = new Collection<EntityMembers>()
            {
              new EntityMembers()
              {
                EntityId = identifier1,
                ModelId = modelId,
                VersionId = identifier2,
                Members = members,
                MemberType = new MemberType?(MemberType.Leaf)
              }
            };
                    DateTime now = DateTime.Now;
                    Collection<Identifier> collection = this.ModelMembersBulkDelete(modelMembers);
                    this.StagingGet(collection, true, true, true, true, ref or);
                    this.ProcessUnbatchedStaging(modelId, identifier2);
                    this.ClearBatch(collection);
                    TimeSpan timeSpan = DateTime.Now.Subtract(now);
                    this.lblError.Text = string.Concat(new object[4]
            {
              (object) "members (bulk) deleted  : ",
              (object) Enumerable.Count<Common.ServiceReference1.Member>((IEnumerable<Common.ServiceReference1.Member>) Enumerable.First<EntityMembers>((IEnumerable<EntityMembers>) modelMembers).Members),
              (object) " --- time elapsed (seconds):",
              (object) timeSpan.TotalSeconds.ToString()
            });
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    Cursor.Current = Cursors.Default;
                }
            }
      public Collection<StagingBatch> StagingGet(Collection<Identifier> stagingBatch, bool ReturnAllCriteria, bool ReturnMembers, bool ReturnAttributes, bool ReturnRelationShips, ref OperationResult or)
            {
                Collection<StagingUnbatchedInformation> UnbatchedInformation = new Collection<StagingUnbatchedInformation>();
                Collection<StagingBatch> collection1 = new Collection<StagingBatch>();
                using (ServiceClient serviceClient = new ServiceClientWrapper().CreateServiceClient())
                {
                    or = new OperationResult();
                    Collection<StagingBatch> collection2 = serviceClient.StagingGet(new International(),true, new StagingResultCriteria()
                    {
                        All = ReturnAllCriteria,
                        Attributes = ReturnAttributes,
                        Members = ReturnMembers,
                        Relationships = ReturnRelationShips
                    }, new StagingSearchCriteria()
                    {
                        StagingBatches = stagingBatch,
                        StagingDataStatus = StagingDataStatus.All
                    }, out or, out UnbatchedInformation);
                    List<string> list = new List<string>();
                    foreach (StagingBatchError stagingBatchError in Enumerable.First<StagingBatch>((IEnumerable<StagingBatch>)collection2).Errors)
                        list.Add(stagingBatchError.ErrorCode + " - " + stagingBatchError.TargetCode);
                    return collection2;
                }
            }
     public void ProcessUnbatchedStaging(Identifier modelId, Identifier versionId)
            {
                using (ServiceClient serviceClient = new ServiceClientWrapper().CreateServiceClient())
                {
                    OperationResult OperationResult = new OperationResult();
                    serviceClient.StagingProcess(new International(), true, new StagingUnbatchedCriteria()
                    {
                        ModelId = modelId,
                        VersionId = versionId
                    }, out OperationResult);
                }
            }

      private void ClearBatch(Collection<Identifier> batches)
            {
                using (ServiceClient serviceClient = new ServiceClientWrapper().CreateServiceClient())
                {
                    OperationResult OperationResult = new OperationResult();
                    int BatchesQueuedToClearCount = 0;
                    int MemberRecordsClearedCount = 0;
                    int RelationshipRecordsClearedCount = 0;
                    serviceClient.StagingClear(new International(), batches, out BatchesQueuedToClearCount, out MemberRecordsClearedCount, out OperationResult, out RelationshipRecordsClearedCount);
                    this.lblError.Text = "BatchesQueuedToClearCount:" + BatchesQueuedToClearCount.ToString() + " -- MemberRecordsClearedCount:" + MemberRecordsClearedCount.ToString() + " -- RelationshipRecordsClearedCount:" + RelationshipRecordsClearedCount.ToString() + (Enumerable.FirstOrDefault<Common.ServiceReference1.Error>((IEnumerable<Common.ServiceReference1.Error>)OperationResult.Errors) != null ? Enumerable.First<Common.ServiceReference1.Error>((IEnumerable<Common.ServiceReference1.Error>)OperationResult.Errors).Code + " - " + Enumerable.First<Common.ServiceReference1.Error>((IEnumerable<Common.ServiceReference1.Error>)OperationResult.Errors).Description : string.Empty);
                }
            }





    Regards,


    Xavier Averbouch
    Microsoft Community Contributor
    SOAT
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

    • Proposed as answer by Elvis Long Tuesday, February 12, 2013 2:20 PM
    Monday, February 11, 2013 4:08 PM
  • I opened a ticket with Microsoft support. They discovered a bug in the staging stored procedure which was missing an index on one of the temp tables (#). The fix they provided was to add a line after the CREATE table statement for temp TableRR to create an index on the StagingID field on TableRR. Here is the line that fixed the issue.

    CREATE CLUSTERED INDEX TableRR_IX1 on #TableRR(StagingId)

    Before adding this line, it took about 12 hours to delete one million records. After adding this line, the same number of records were deleted in half an hour. The MDS product version that I am working with is 11.0.2218.0 and the MDS database version is 11.0.2.1.

    Thanks & Regards,

    Kamal

    • Marked as answer by KamalShaikh Thursday, February 14, 2013 9:45 PM
    Thursday, February 14, 2013 9:45 PM