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

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.

  • Thank you @sfdcfox! I have updated the question because of limited char length here. Please look at the updated section. – SunnyG Feb 25 at 21:15
  • @SunnyG you need to use LIKE if you want to use the % wildcard. – sfdcfox Feb 25 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 at 21:41

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.