Optimize Extraction of Reports Metadata Code to decrease CPU Time

Question

I'm struggling to optimize my current code that, at the moment, retrieves report metadata of 25 reports. While it works well, I'm getting awfully close to the maximum cpu time as shown below. I realize I need to increase efficiency and make it so it uses less cpu time.

I have tried a few things I found like declaring all relevant variables before the loop so that they don't get initialized every time in the loop, but it really didn't help. I also tried to change the way my loop runs like using a traditional for-loop, for-each, storing the size of the array in a variable etc.

The code I have right now retrieves all the relevant information about a report, formats and compiles it into a csv file, and then sends that file as an email attachment to the user's email. The report api names are stored in a txt file which I have uploaded as a static resource, and my code will be using that to loop through each report and collect the required information.

StaticResource sr = [select body from StaticResource Where Name = 'test2'];
List<String> reportNames = sr.body.toString().replaceAll('\r\n','\n').replaceAll('\r','\n').split('\n');
Report[] reportsInfo = [SELECT Name, FolderName FROM Report WHERE DEVELOPERNAME = :reportNames];

// to hold final result
string result = '';

for(Report reportRecord: reportsInfo) {
    
    // get report folder api name
    String fname = (String) reportRecord.get('FolderName');
    List <Folder> folderAPI = [SELECT DEVELOPERNAME FROM FOLDER where Name = :fname];
    
    // Get report ID
    String reportId = (String)reportRecord.get('Id');
    
    // Use report ID to get report Metadata
    Reports.ReportMetadata metadata = Reports.ReportManager.describeReport(reportId).getReportMetadata();

    // Run a report
    Reports.reportResults results = Reports.ReportManager.runReport(reportId, true);

    // Get the report extended metadata
    Reports.ReportExtendedMetadata rmd = results.getReportExtendedMetadata();

    // begin collecting and formatting data
    string header = 'Report Info \n\n';
    String finalstr = header ;
    
    //variable to store report name 
    String repName = metadata.getName() + ' , ';
    
    // report environment, date/time program was executed
    string recordString01 = repName+ 'Environment: , ' + URL.getSalesforceBaseUrl().getHost() + '\n' + repName+
                            'Date/Time of Execution: , ' + System.now().format() + '\n\n';
    
    // report name, type, folder
    string recordString1 = repName +'Report Name: , '+metadata.getName() +'\n'+ repName +'Report API Name: , '+
                           metadata.getDeveloperName() +'\n' + repName + 'Report Type Name: , ' + metadata.getReportType().getLabel() + '\n'
                           +repName +'Report Type API Name: , '+ metadata.getReportType().getType() + '\n'+repName +
                           'Report Folder: , ' + fname +'\n' +repName + 'Report Folder API Name: , ' + folderAPI.get(0).get('DEVELOPERNAME') 
                           + '\n'+ repName +'Report Description: , ' + metadata.getDescription() +'\n\n';


    // report filters, date, scope
    string recordString11 = repName +'Report Filters: \n' +repName +'Report Created Date: '+ metadata.getStandardDateFilter().getStartDate()+
        ' , Report End Date: ' + metadata.getStandardDateFilter().getEndDate() +' , Report Scope: ' + metadata.getScope() + '\n';
    
    for(Reports.ReportFilter rf : metadata.getreportFilters()){
        recordString11 += repName +'Filter Operator: ' + rf.getOperator() + ', Filter Name: '+rf.getcolumn() + ',  Filter Value: ' + rf.getValue() + '\n';        
    }
        
    // report cross filters
    string recordString12 = repName + 'Report Cross Filters: \n';
    for(Reports.CrossFilter rf : metadata.getCrossFilters()){
        recordString12 += repName + 'Filter Object: '+rf.getRelatedEntityJoinField() + ',  Filter Value: '+rf.getRelatedEntity() + '\n';
    }
    
    // report date, scope
    string recordString2 =  recordString11 +'\n\n' + recordString12 +'\n\n';

    
    // report groupings info
    Map<String,Reports.GroupingColumn> groupings = rmd.getGroupingColumnInfo();
    string recordString3 = repName + 'Groups: \n';
    
    // used to give numbered list in output
    Integer count_a = 1;
    
    for (String key : groupings.keySet()) {
        recordString3 += repName + count_a + ': ' + groupings.get(key).getLabel() + '\n';
        /*+ ' , Type: ' + groupings.get(key).getDataType() */
        //This line gives the datatype of the groupings and is optional to use
        count_a += 1;
    }
    recordString3 += '\n';
    
    // report column info
    Map<String,Reports.DetailColumn> colMap = rmd.getDetailColumnInfo();
    
    // used to give numbered list in output
    Integer count_b = 1;
    string recordString4 = repName + 'Columns: \n';
    for(String key : colMap.KeySet()){
        recordString4 += repName + count_b + ': ' + 'Column Label =  ' + colMap.get(key).getLabel() + ' ,  Column API Name = ' + Key + '\n';
        count_b += 1;
    }
    recordString4 += '\n';

    
    // report fields info
       
    // set new http callout request 
    Http http = new Http();
    HttpRequest req = new HttpRequest();
    
    // use rest api with appropriate parameters to get info about report's fields and its parent object 
    req.setEndpoint(URL.getSalesforceBaseUrl().toExternalForm() + '/services/data/v51.0/analytics/reports/' + reportId + '/describe');
    req.setHeader('Authorization', 'Bearer ' + UserInfo.getSessionId());
    req.setMethod('GET');
    
    HttpResponse res = http.send(req);
    Map<String, Object> resBody = (Map<String, Object>) JSON.deserializeUntyped(res.getBody());
    Map<String, Object> reportExtendedMetadata = (Map<String, Object>) resBody.get('reportExtendedMetadata');
    Map<String, Object> detailColumnInfo = (Map<String, Object>) reportExtendedMetadata.get('detailColumnInfo');
    
    String recordString5 = repName + 'Field and its Parent Object: \n';
    for (String key : detailColumnInfo.keySet()) {
        Map<String, Object> tmp = (Map<String, Object>) detailColumnInfo.get(key);
        recordString5 += repName + (String) tmp.get('label') + ' -> ' + (String) tmp.get('entityColumnName') + ' -> ' + ((String) tmp.get('entityColumnName'))?.substringBefore('.') + '\n'; 
        // E.g, Account Name -> Account.Name -> Account
    }
    
    
    //putting it all together
    finalstr = finalstr + recordString01 +recordString1 + recordString2 + recordString3 + recordString4  + recordString5 + '\n\n';
    result += finalstr;   
  
    System.debug(finalstr);

    
}

Messaging.EmailFileAttachment csvAttc = new Messaging.EmailFileAttachment();
blob csvBlob = Blob.valueOf(result);
string csvname = 'ReportMetadata.csv';
csvAttc.setFileName(csvname);
csvAttc.setBody(csvBlob);
Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();
String[] toAddresses = new list<string> {'[email protected]'};
String subject = 'Reports Metadata';
email.setSubject(subject);
email.setToAddresses( toAddresses );
email.setPlainTextBody('Attatched to this email is the report metadata csv file. ');
email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvAttc});
Messaging.SendEmailResult [] r = Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});

Any help is appreciated. Thanks.

Answer 1

Ok, I know I shouldn't be just writing the code, but this one was interesting and used a pattern that I am keen on - a repeating queueable.

Basically, you get your list of reports and run your summarization on each (one or a few per queueable). You make sure to pass the serialized results on to each iteration and when you are done, you send the email. Like so:

public class ReportQueue implements Queueable {

  private Integer CHUNK_SIZE = 5;
  private String serializedReportMetadata = '';
  
  //this would be a list of your reports info query results
  private Report[] reports {get; set;}

  public ReportQueue(Report[] reports, serializedReportMetadata) {
    this.reports = reports;
    this.serializedReportMetadata += serializedReportMetadata;
  }

  public void execute(QueueableContext context) {

    Report[] reportsRemaining = (sObject[])ListUtil.slice(reports, 0, this.CHUNK_SIZE);
    
    //this could be a callable (to make dynamic) or just a part of your report runner class
    String reportMetadata = ReportSummarizer.getMetadata(reportsRemaining);
    
    //normally I'd do this BEFORE doing the above work, but we need the results to feed into the next call
    if (reports.size() > this.CHUNK_SIZE) {
      Report[] reportsRemaining = (sObject[])ListUtil.slice(reports, this.CHUNK_SIZE, reports.size() );
      ReportQueue rq = new ReportQueue(reportsRemaining, reportMetadata);
      Id jobID = Test.isRunningTest() ? null : System.enqueueJob(rq);
    }
    //we are done! Send off the Email!!
    else {
      ReportSummarizer.sendEmail(this.serializedReportMetadata + reportMetadata);
    }
  }
}

You'll need my list util for this too:

public with sharing class ListUtil {

  public static SObject[] slice(SObject[] sourceList, Integer bugin, Integer endd) {
    SObject[] resultList = new SObject[] {};

    if (sourceList == null) {
      return resultList;
    }
    Integer len = sourceList.size();

    // Handle negative value for "bugin"
    Integer start = bugin;
    start = (start >= 0) ? start : Math.max(0, len + start);

    // Handle negative value for "endd"
    Integer upTo = Math.min(endd, len);
    if (endd < 0) {
      upTo = len + endd;
    }

    // Actual expected size of the slice
    Integer size = upTo - start;

    if (size > 0) {
      for (Integer i = 0; i < size; i++) {
        SObject obj = sourceList[start + i];
        if (obj != null) {
          resultList.add(obj);
        }
      }
    }
    return resultList;
  }
}

To use the code above, run your soql query, then enqueue the job with the results of that query. When the job is done, it will take all the data generated and make the email.

StaticResource sr = [select body from StaticResource Where Name = 'test2'];
List<String> reportNames = sr.body.toString().replaceAll('\r\n','\n').replaceAll('\r','\n').split('\n');
Report[] reportsInfo = [SELECT Name, FolderName FROM Report WHERE DEVELOPERNAME = :reportNames];

ReportQueue rq = new ReportQueue(reportsInfo, '');
System.enqueueJob(rq);

Inside this ReportQueue class, it will get the metadata in batches. Each batch has it's own CPU limits. Each batch passes the results on to the next batch and and appends it's own results.

Finally, it sends the email.

The only thing remaining for you to do is change your original class so it can be called by this Queueable class - this bit:

String reportMetadata = ReportSummarizer.getMetadata(reportsSubset);

Answer 2

There are lots of things to improve here. You have multiple loops inside a loop. You have to build your logic such that all the loops remains separate. For e.g. here is the initial code which I have modified:

set<String> setFolderName = new set<String>();
set<String> setRepIds = new set<String>();
list<Reports.ReportMetadata> lstMdt = new list<Reports.ReportMetadata>();

for(Report reportRecord: reportsInfo) {
    setFolderName.add((String) reportRecord.get('FolderName'));
    setRepIds.add((String)reportRecord.get('Id'));
}
List <Folder> folderAPI = [SELECT DEVELOPERNAME FROM FOLDER where Name IN:setFolderName];
for(String reportId : setRepIds){
    lstMdt.add(Reports.ReportManager.describeReport(reportId).getReportMetadata());
}

Likewise, you can optimise the other code to get best results.