Tjyylli

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:

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>

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.