0

I am running into a CPU limit error when processing more than + or - 300 rows from a .csv file, via a Visualforce page/controller. I realize CPU limits can have a lot to do with other items running in the background (WF, PB, Validation, other triggers, etc.).

I'm wondering, however, if there is any way I can enhance the code below to reduce CPU usage. For example, say a user uploads a .csv file via the Visualforce page, and the file has 300 rows. What would be the best method to process the first 200 rows of the .csv file > Save to database > process the last 100 rows of the .csv file?

You can see that for() loop in the code below processes each of .csv lines individually > adds them to a list > a Database.update method is used to update the records in the list.

CONTROLLER

public class updateChannelLeads {
    
public Blob csvFileBody{get;set;}
Public string csvAsString{get;set;}
Public String[] csvfilelines{get;set;}
Public List <Lead> sObjectList {get;set;}
public List <Lead> leadsToUpdateSuccess {get;set;}
public List <Lead> leadsToUpdateError {get;set;}
Public String userName {get;set;} 
    
    
public updateChannelLeads()
   {
    csvfilelines = new String[]{};
    sObjectList = New List<Lead>();

   }
    Public void updateLeads(){
      
        leadsToUpdateSuccess = New List<Lead>();
        leadsToUpdateError = New List<Lead>();
        User currentUser = [SELECT Id, Name FROM User WHERE Id = :userinfo.getUserId()];
        userName = currentUser.Name;
        csvAsString = csvFileBody.toString();
        csvfilelines = csvAsString.split('\n');

       for(Integer i=1;i<csvfilelines.size();i++)
           try{
               String[] csvRecordData = csvFileLines[i].split(',');
               if(string.isNotBlank(csvRecordData[1])){
         {
           String LeadId = csvRecordData[0];
           String ResellerId = csvRecordData[1];
             System.debug('LeadId: '+LeadId);
             System.debug('ResellerId: '+ResellerId);
           String LeadIdNormalized = LeadId.normalizeSpace();
           String ResellerIdNormalized = ResellerId.normalizeSpace();
             System.debug('LeadId: '+LeadIdNormalized);
             System.debug('ResellerId: '+ResellerIdNormalized);
           Lead lObj = new Lead();
             System.debug(lObj);
           lObj.Id = Id.valueOf(LeadIdNormalized);
             System.debug(lObj);
           lObj.Reseller__c = Id.valueOf(ResellerIdNormalized);
             System.debug(lObj);
           lObj.ProcessedByUpdateChannelLeadPage__c = TRUE;
           lObj.DateTimeProcessedByUpdateChannelLeadPage__c = system.now();
           sObjectList.add(lObj);
             System.debug('List: '+sObjectList);

            }
         }

       }
                
        catch(Exception e)  
              {
                ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.Error,'NOTE: all Leads that did not result in an error were successfully updated.  Error Message: '+e.getMessage());
                ApexPages.addMessage(errorMessage);
              }
            Database.SaveResult[] srList = Database.update(sObjectList, false);
            for (Database.SaveResult sr : srList){
                if(sr.isSuccess()){
                    System.debug('Successfully updated Lead. Lead Id: '+sr.getId());
                }
                else{
                    for(Database.Error err : sr.getErrors()){
                        System.debug('The following error has occurred.');
                        System.debug(err.getStatusCode() + ': '+err.getMessage());
                        System.debug('Lead fields that affected this error: '+err.getFields());
                        ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.Error,'Some of the Leads were not updated, due to the following error.  Error Message: '+err.getMessage()+' | '+err.getFields());
                        ApexPages.addMessage(errorMessage);
                                                                    

                    }
                     
                }


            }
                leadsToUpdateSuccess = [Select Id, X18_digit_id__c, Reseller__c, ResellerId__c, LastModifiedBy.Name, LastModifiedDate, ProcessedByUpdateChannelLeadPage__c, DateTimeProcessedByUpdateChannelLeadPage__c FROM Lead WHERE Id IN:sObjectList AND LastModifiedBy.Name = :userName AND LastModifiedDate = TODAY AND Reseller__c != NULL];
                System.debug('leadsToUpdateSuccess: '+leadsToUpdateSuccess);

                leadsToUpdateError = [Select Id, X18_digit_id__c, Reseller__c, ResellerId__c, Name, ProcessedByUpdateChannelLeadPage__c, DateTimeProcessedByUpdateChannelLeadPage__c FROM Lead WHERE Id IN:sObjectList AND Reseller__c = NULL AND ProcessedByUpdateChannelLeadPage__c = FALSE AND DateTimeProcessedByUpdateChannelLeadPage__c = null];
                System.debug('leadsToUpdateError: '+leadsToUpdateError);

        }
        
    }

3 Answers 3

1

You'll want to use Batch Apex for this. It will allow you to update a large number of records in predefined chunks so that you can avoid running into limits like this.

1
  • if I were to implement Batch Apex, which method would you place in the Visualforce commandButton? For Example: <apex:commandButton id="UpdateLeads" value="Update Channel Leads" action="{!updateLeads}"/>. This commandButton calls the current method in the controller. If I were to implement Batch Apex, what would I put in the commandButton? I would have start, execute, and finish methods.
    – Jklotzle
    May 18, 2021 at 15:16
0

It'd be easier to use papaparse, parse the csv client side, and use Visualforce remoting to insert/update/delete records.

0

Another approach would be to delegate the work to a queueable which will have 60 secs of CPU time. For large CSV files, if you want to do all the work in Apex:

  • upload the file and save as a ContentVersion
  • launch a batchable that uses an Iterable in the start() method to fetch each row that gets passed to execute() in the scope arg (200 objects at a time if using the default)

CSV file processing in apex is tricky when the files get large

  • You could run out of Heap or Viewstate in the VF controller if you forget to use transient
  • If you delegate to a queueable, you could still run out of CPU (60) time or DML row limits (10,000) - but at least if the transaction fails, everything is rolled back
  • If you delegate to a batchable, you effectively have unlimited CPU but will still run into heap issues for the String representing the CSV body. Batchable also runs into the issue of what to do when some rows in one or more execute() transactions fail - do you roll back everything or generate an errors.csv file like the way Dataloader works.

Doing the processing on the client side as sfdcfox recommends eliminates all the heap and cpu issues, provides direct user feedback, but still will require what to do if some rows fail across VF Remoting transactions.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .