1

I am getting Apex CPU time limit exceeded even though the listhistoricalList is small (has 14 Records in it).

String soql = 'SELECT Id, Name, SoM_Business_Location_Status__c, SoM_Shipping_Address__c, SoM_ShippingAddress2__c, SoM_Shipping_CityStateZip__c, ' +
    'Primary_Contact_Signing_Authority__r.Name, DBA__c, SoM_County__c, Current_LGU_Account__c, SoM_LARA_Business_ID__c ' +
    ',(SELECT Id FROM Complaint_Violations__r LIMIT 1) ' +
    'FROM Account ';
    String whereClause = '';
    if (accountIds.size() > 0) {
        whereClause += 'WHERE Id IN :accountIds ';
    } else {
        whereClause += 'WHERE Id!=null ';
    }

    // Query and populate the list
    licensees = Database.query(soql + whereClause + soqlFilter + 'LIMIT 10001 ');
    // Get record count and provide warning message if necessary
    if (licensees.size() == 10001) {
        licensees.remove(licensees.size() - 1);
        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.Warning,
                                                   'Warning! More than 10,000 records found.' +
                                                   ' All records are not displayed. Please consider using filter' +
                                                   ' to limit the number of records.'));
    }

    // Prepare account set to get historical sales list for the licensees
    Set<Id> accountSet = new Set<Id> ();
    for (Account acc : licensees) {
        accountSet.add(acc.Id);
        accountPreviousYearSales.put(acc.Id, 0);
        accountNextYearSales.put(acc.Id, 0);
    }
    // Query and populate account sales map
    List<Historical_Sales_Records__c> historicalList = [SELECT Id, Account__c, Sales_Amount__c, Date__c, Year__c
                                                        FROM Historical_Sales_Records__c
                                                        WHERE Account__c = :accountSet];
    for (Historical_Sales_Records__c s : historicalList) {
        if (s.Date__c != null) {
            if (Date.valueOf(s.Date__c).year() == System.today().year() - 1) {
                accountPreviousYearSales.put(s.Account__c, accountPreviousYearSales.get(s.Account__c) + s.Sales_Amount__c);
            } else if (Date.valueOf(s.Date__c).year() == System.today().year()) {
                accountNextYearSales.put(s.Account__c, accountNextYearSales.get(s.Account__c) + s.Sales_Amount__c);
            }
        }
    }

getting Apex CPU Time limit Exceeded at line: if (Date.valueOf(s.Date__c).year() == System.today().year() - 1)

Please see this image:

enter image description here

UPDATED SECTION

Made these changes:

Set<Id> accountSet = new Set<Id> ();
        for (Account acc : licensees) {
            accountSet.add(acc.Id);
            accountPreviousYearSales.put(acc.Id, 0);
            accountNextYearSales.put(acc.Id, 0);
        }

        Integer previousYear = Date.today().year()-1;
        Integer currentyear = Date.Today().year();
        string previousyearstring = '%'+string.valueof(previousYear)+'%';  
        string currentyearstring = '%'+string.valueof(currentyear)+'%'; 

        AggregateResult[] data = [
            SELECT Account__c AccountId, Year__c year, SUM(Sales_Amount__c) sum
            FROM Historical_Sales_Records__c
            WHERE Account__c = :accountSet AND (Year__c = :previousyearstring OR Year__c = :currentyearstring) GROUP BY Account__c, Year__c
        ];

        for(AggregateResult item: data) {
            String year = (String)item.get('year');
            Decimal sum = (Decimal)item.get('sum');
            Id accId = (Id)item.get('AccountId');
            (currentyearstring == year? accountNextYearSales: accountPreviousYearSales).put(accId, sum);
        }

Now, i am not getting this value in VF page:

<apex:repeat value="{!licensees}" var="acc">
                                                        <tr>
                                                            <td>
                                                                <a href="#" onclick="showDetail('{!acc.Id}'),setScrollTo('#details')">{!acc.SoM_LARA_Business_ID__c}</a>
                                                            </td>
                                                            <td>{!acc.Name}</td>
                                                            <td>{!acc.DBA__c}</td>
                                                            <td>{!acc.Current_LGU_Account__c}</td>
                                                            <td>{!acc.SoM_County__c}</td>
                                                            <td>
                                                                <apex:outputtext value="{!acc.SoM_Shipping_Address__c} {!acc.SoM_ShippingAddress2__c} {!acc.SoM_Shipping_CityStateZip__c}" escape="false" />
                                                            </td>
                                                            <td>{!acc.SoM_Business_Location_Status__c}</td>
                                                            <td>
                                                                <a href="#" onclick="showDetail('{!acc.Id}'),setScrollTo('#violations')">{!if(acc.Complaint_Violations__r.size>0,'Y','N')}</a>
                                                            </td>
                                                            <td>${!accountNextYearSales[acc.Id]}</td>
                                                            <td>${!accountPreviousYearSales[acc.Id]}</td>
                                                        </tr>
                                                    </apex:repeat>

enter image description here

1 Answer 1

3

today() was called 5082 times, meaning there were at least 2541 records in the list. That whole mess could be avoided if you used an aggregate result query:

AggregateResult[] data = [
  SELECT Account__c AccountId, CALENDAR_YEAR(Date__c) year, SUM(Sales_Amount__c) sum
  FROM Historical_Sales_Records__c
  WHERE Account__c = :accountSet AND Date__c >= LAST_YEAR AND Date__c > NEXT_YEAR
  GROUP BY Account__c, CALENDAR_YEAR(Date___c)
];
Decimal thisYear = Date.today().year();
for(AggregateResult item: data) {
  Decimal year = (Decimal)item.get('year'), sum = (Decimal)item.get('sum');
  Id accId = (Id)item.get('AccountId');
  (thisYear == year? accountNextYearSales: accountPreviousYearSales).put(accId, sum);
}

This is also a case where moving the filters in to the query drastically reduces the number of rows returned, increasing performance.

3
  • Thank you @sfdcfox! I have updated the question because of limited char length here. Please look at the updated section.
    – SunnyG
    Feb 25, 2019 at 21:15
  • @SunnyG you need to use LIKE if you want to use the % wildcard.
    – sfdcfox
    Feb 25, 2019 at 21:36
  • Ohhhhh! I didn't realize i still had % there. I am getting the values now. I will check if the time limit error is gone. Thanks
    – SunnyG
    Feb 25, 2019 at 21:41

You must log in to answer this question.

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