0

We upload Revenue Data on monthly basis in our system. For this we have created a Custom Object Revenue__c that stores the information of Revenue Data.We have created a Custom Application where with the help of VF page, we upload the Revenue Data on monthly basis.

In order to upload the data we prepare csv file which contains Revenue Data and upload the file to the system using Add File & Add more file option. For this VF page is designed that calls Controller and then batch class runs to insert the data in system.

For a particular month we add only one file per month, if we want to add more files for particular month, with the help of Add more file option we can upload it.

At certain stage when I try to import the file, I face DML:10001 error. The error is coming when Revenue and Analysis contains more than 5000 records.

The error is coming in below batch class start methods:

global with sharing class SyncRunBatch implements Database.Batchable<sObject>, Database.Stateful
{
    private ImportFile__c importFile;
    private id importFileId;
    private id importFileTypeId;

    public SynchronisationRunBatch(ImportFile__c file)
    {
        importFile = file;
        importFileId = file.Id;
        importFileTypeId = file.ImportFileType__c;
    } 

    global Database.QueryLocator start(Database.BatchableContext info)
    {
        system.debug('SynchronisationRunBatch Start Method Called:');
        // update the Import File record and set it to SynchronisationProcessing
        ImportFile__c importFile = [select id, ApexJobId__c, Status__c, ForecastingTimePeriod__c, BusinessUnit__c from ImportFile__c where Id =:importFile.Id];
        system.debug('CsvParseAndUploadBatch Import File Status'+importFile.Status__c);
        system.debug('CsvParseAndUploadBatch ImportFileStatusEnum File Status:'+ImportFileStatusEnum.AttachmentProcessingId);
        if (importFile.Status__c != ImportFileStatusEnum.SynchronisationProcessingId)
        {
            importFile.Status__c = ImportFileStatusEnum.SynchronisationProcessingId;
            update importFile;
        }

        // When adding files to a RevenueCost import file, delete all records so that a full synchronisation is completed again.      
        if (importFileTypeId == ImportFileTypeEnum.DirectRevenueCostId)
        {
            // Firstly delete the existing EngagementRevenueCostValue records 
            List<EngagementRevenue__c> revenueRecordsToDelete = [select id from EngagementRevenue__c where RevenueCostValue__r.ImportFile__r.Id = :importFile.Id LIMIT 10000];
            system.debug('revenueRecordsToDelete:'+revenueRecordsToDelete);
            while (revenueRecordsToDelete.size() > 0)
            {
                delete revenueRecordsToDelete;
                revenueRecordsToDelete = [select id from EngagementRevenue__c where RevenueCostValue__r.ImportFile__r.Id = :importFile.Id LIMIT 10000];
                system.debug('After Delete Records:'+revenueRecordsToDelete);
            }

            // Secondly delete the existing  Analysis records 
            List<Analysis__c> analysisRecordsToDelete = [select id from Analysis__c 
                                                                            where (AnalysisFact__c = :AnalysisFactEnum.CorporateRevenueId or AnalysisFact__c = :AnalysisFactEnum.CorporateCostId)
                                                                            and TimePeriod__c = :importFile.ForecastingTimePeriod__c 
                                                                            and BusinessUnit__c = :importFile.BusinessUnit__c LIMIT 10000];
            system.debug('Analysis Records Delete:'+analysisRecordsToDelete);                                                               
            while (analysisRecordsToDelete.size() > 0)
            {
                delete analysisRecordsToDelete;
                analysisRecordsToDelete = [select id from Analysis__c 
                                        where (AnalysisFact__c = :AnalysisFactEnum.CorporateRevenueId or AnalysisFact__c = :AnalysisFactEnum.CorporateCostId)
                                        and TimePeriod__c = :importFile.ForecastingTimePeriod__c 
                                        and BusinessUnit__c = :importFile.BusinessUnit__c LIMIT 10000];
             system.debug('Analysis Records Delete1:'+analysisRecordsToDelete); 
            }

        }

        string fileId = importFile.Id;
        string query = (importFileTypeId == ImportFileTypeEnum.FlashId || importFileTypeId == ImportFileTypeEnum.AllocationId)
                        ? 'select id, ImportFile__r.BusinessUnit__c, Period__c, ImportFile__r.ImportFileType__c, ImportFile__r.TimePeriod__r.TimePeriod__r.Id, ImportFile__r.TimePeriod__r.TimePeriod__r.StartDate__c, ProjectNumber__c, EmployeeNumber__c, Resource__c, Resource__r.TimePatternVariant__r.TimePattern__r.HoursPerDay__c from Allocation__c where ImportFile__c = :fileId'
                        : 'select id, ImportFile__r.BusinessUnit__c, ImportFile__r.ImportFileType__c, ImportFile__r.TimePeriod__r.TimePeriod__r.Id, ImportFile__r.TimePeriod__r.StartDate__c, ProjectNumber__c from Revenue__c where ImportFile__c = :fileId';

        return Database.getQueryLocator(query);
    }     

    // Retrieves the Synchronisation Run and executes it
    global void execute(Database.BatchableContext info, List<sObject> scope)
    {
        system.debug('SynchronisationRunBatch execute Method Called:');
        SyncRun syncRun = SyncRunFactory.GetProcess(importFileTypeId);
        syncRun.execute(scope);
    }
    global void finish(Database.BatchableContext info)
    {
            importFile.Status__c = ImportFileStatusEnum.CompletedId;
            update importFile;
    } 
}

If you can see the start method, It deletes existing records of Revenue and Analysis object then in execute method, it calls SyncRun Factory that Inserts the new records.

Below is the code that insert Revenue Records in the system:

public with sharing class SynchronisationRuns
{
   public with sharing class RevenueCostValues implements SyncRun
  {
    public void Execute(List<SObject> scope)
    {
      system.debug('RevenueCostValues Execute');
      Id periodId;
      Set<id> deliveryGroupIds = new Set<id>();
      List<EngagementRevenue__c> newEngRevCostValues = new List<EngagementRevenue__c>();
      // bulkify retrieval of order project records
      Map<string, Order__c> orderMap = new Map<string, Order__c>();
      for (Order__c orderRecord : [select Id, Name, (select Group__c from OrderAllocations__r where Group__c != null) from Order__c])
      {
        orderMap.put(orderRecord.Name, orderRecord);
      }
      system.debug('orderMap val:'+orderMap);
      // used to store the new  analysis records that will store the file import value for this forecasting period
      List<Analysis__c> paRecords = new List<Analysis__c>();
      id servicesDomainClassId = [select id from DomainClass__c where Enum__c = 'Services'][0].Id;

      // iterate over the records
      for (SObject obj  : scope)
      {
        RevenueCostValue__c newRevCostValue = (RevenueCostValue__c) obj;

        // match the order based on the ProjectNumber
        Order__c matchingOrder = (Order__c) orderMap.get(string.valueOf(newRevCostValue.ProjectNumber__c.setScale(0)));
        boolean isMatch = false;

        if ((matchingOrder != null) && (matchingOrder.OrderAllocations__r.size() > 0))
        {
          for (OrderAllocation__c orderAlloc : matchingOrder.OrderAllocations__r)
          {
            if (orderAlloc.Group__c != null)
            {
            system.debug('Enagagment Exists for Order');    
              isMatch = true;
              // Create a new EngagementRevenueCostValue record
              EngagementRevenue__c newEngRevCostValue = new EngagementRevenue__c();
              newEngRevCostValue.Order__c = matchingOrder.Id;
              newEngRevCostValue.RevenueCostValue__c = newRevCostValue.Id;
              newEngRevCostValue.Group__c = orderAlloc.Group__c;
              newEngRevCostValues.add(newEngRevCostValue);
              deliveryGroupIds.add(orderAlloc.Group__c);
            }
          }
        }

        if (!isMatch)
        {
          system.debug('Enagagment Does not Exists for Order');
          EngagementRevenue__c newEngRevCostValue = new EngagementRevenue__c();
          newEngRevCostValue.RevenueCostValue__c = newRevCostValue.Id;
          newEngRevCostValues.add(newEngRevCostValue);  
        }
      }

      Map<id, SyncService.DeliveryGroupAnalysis> analysisMap = SyncService.GetTheDeliveryGroupAnalyses(deliveryGroupIds, ((RevenueCostValue__c)scope[0]).ImportFile__r.ForecastingTimePeriod__r.StartDate__c);

      for (EngagementRevenue__c rcv : newEngRevCostValues)
      {
        // Only populate those that have a Delivery Group, i.e. they have been matched with an existing OrderAllocation
        if (rcv.Group__c != null)
        {
          // Sets the current Performance Analysis Values on the rcv record
          SyncService.DeliveryGroupAnalysis analysis = (SyncService.DeliveryGroupAnalysis)analysisMap.get(rcv.Group__c);

           rcv.ServicesActualRevenue__c = analysis.ServicesActualRevenue;
           rcv.ServicesP1Revenue__c = analysis.ServicesP1Revenue;
           rcv.ServicesActualCost__c = analysis.ServicesActualCost;
           rcv.ServicesP1Cost__c = analysis.ServicesP1Cost;
           rcv.ExpensesActualRevenue__c = analysis.ExpensesActualRevenue;
           rcv.ExpensesP1Revenue__c = analysis.ExpensesP1Revenue;
           rcv.ExpensesActualCost__c = analysis.ExpensesActualCost;
           rcv.ExpensesP1Cost__c = analysis.ExpensesP1Cost;
           system.debug('Creating Analysis Values on the Engagement Revenue Cost Value record');
        }
      }

      // insert the new EngagementRevenueCostValue records
      if (newEngRevCostValues.size() > 0)
      {
        insert newEngRevCostValues;
      }

               for (EngagementRevenue__c rcv : [select RevenueCostValue__r.Parameter__c, RevenueCostValue__r.ImportFile__r.ForecastingTimePeriod__r.Id, Group__c, Group__r.Account__c, RevenueCostValue__r.ImportFile__r.BusinessUnit__c, RevenueCostValue__r.AmountUSD__c
                            from EngagementRevenue__c
                            where id in :newEngRevCostValues])
      {
        // Only creates records for those that have a Delivery Group, i.e. they have been matched with an existing OrderAllocation
        if (rcv.Group__c != null)
        {
           // create a new Performance Analysis record for either CorporateRevenue or CorporateCost
           Analysis__c pa = new Analysis__c();
           pa.AnalysisFact__c = rcv.RevenueCostValue__r.Parameter__c == 'Revenue' ? AnalysisFactEnum.CorporateRevenueId : AnalysisFactEnum.CorporateCostId;
           pa.DomainClass__c = servicesDomainClassId;
           pa.TimePeriod__c = rcv.RevenueCostValue__r.ImportFile__r.ForecastingTimePeriod__r.Id;
           pa.Group__c = rcv.Group__c;
           pa.CurrencyIsoCode = 'USD';
           pa.Account__c = rcv.Group__r.Account__c;
           pa.BusinessUnit__c = rcv.RevenueCostValue__r.ImportFile__r.BusinessUnit__c;
                  pa.Actual__c = rcv.RevenueCostValue__r.AmountUSD__c;
                  pa.P1Forecast__c = rcv.RevenueCostValue__r.AmountUSD__c;
                  pa.P2Forecast__c = rcv.RevenueCostValue__r.AmountUSD__c;
                  pa.P3Forecast__c = rcv.RevenueCostValue__r.AmountUSD__c;

           paRecords.add(pa);
           system.debug('Creating Analysis Records:');
        }
      }

      // insert the new PerformanceAnalysis records
      if (paRecords.size() > 0)
      {
        insert paRecords;
      }
    }
  }
}

Can someone plz suggest how can I fix the DML:10001 error in such case?

2
  • 2
    There are so many queries/DML statments in Start method even there are queries/ DML statments in while loop which is totally disaster. Anyway DML update only allows 10000 records in one transaction and as you are using DMLs in loop then in total this number is exceeding. Too much refactoring is required from your side.
    – Mr.Frodo
    Jul 9 '18 at 11:14
  • what would be ur recommendation. I was wondering instead of deleting those records if I can use Upsert instead of Insert that would resolve the issue. But looking for experts suggestion here
    – sfdcbeast
    Jul 9 '18 at 11:16
1

If your business need is to delete all records, then insert all new records, you'd want to run two batches: the first batch would delete all existing records, the second would insert all of the new records. This is probably not a good idea overall, though; if possible, you should write your code in a way that allows you to determine which records to insert, which to update, and which to delete.

I'd say that could probably flip this around by upserting (insert and update) on all records, keeping track of what records were touched, and then deleting everything left over. This would likely give much better performance than deleting everything first. You would still probably want two batches for this, but the second batch would just query any records not modified recently and delete them, or use some sort of tagging system to determine which records can be deleted.

1
  • 1
    I have removed the delete logic which is mentioned in batch classes. Instead of deleting the existing records, I've created Rollup summery field on Revenue object(Revenue Object is the Master of one of the child object in our application). Before Inserting Revenue records, I am checking if the Rollup summery field is 0 only then I am inserting the records. For existing records Rollup count is 1 so the it will never create duplicate records. That helps to resolve the issue. Thanks for suggestion ;)
    – sfdcbeast
    Jul 10 '18 at 15:09
0

As @Mr.Frodo pointed out you have too much database operations in loops in your code. This is not good and against Apex coding best practises:

1. Apex Code Best Practices

2. Apex Best Practices: The 15 Apex Commandments

I would recommend getting familiarized with the above, which would be beneficial in long term and when you be refactoring that mess.

As quick help I may suggest that you reduce the scope of records entering the execute method in your SyncRunBatch class. I don't know what's the current number in your setup, but the default is 200. So when invoking your batchable class set the scope to be 100 for instance and see how it goes.

This is pseudo code:

Database.executeBatch(new SyncRunBatch (), 100);

executeBatch(batchClassObject, scope) - docs

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.