2

Is it efficient to calculate AVG using aggregate function, using a group by clause. Like:

(Select Designation__c,AVG(Salary) from Employee__c GROUP BY Designation__c)

Or is it efficient to fetch all records and calculate their average in apex code.

I have a find the average to prepare visual charts over a large data set.

7

Well, the easiest way to figure that out is to do some benchmarking. Adrian Larson's Apex Limits Profiler can help out with that, but I'm feeling a bit lazy, and this seems like a simple enough test.

My test code is as follows:

Decimal average;
// Profiling using SOQL aggregate function
Decimal startTime = Limits.getCpuTime();
DateTime startDT = DateTime.now();
average = 0;
List<AggregateResult> ar = [SELECT AVG(MRR__c) FROM Account WHERE Account_Status__c = 'Active' AND RecordType.Name = 'Billing'];
average = (Decimal)ar[0].get('expr0');
DateTime endDT = DateTime.now();
Decimal endTime = Limits.getCpuTime();

system.debug(System.LoggingLevel.INFO, 'time to execute: ' + (endTime - startTime));
system.debug(System.LoggingLevel.INFO, 'execution in ms: ' + (endDT.getTime() - startDT.getTime()));

// Profiling using normal query + loop

startTime = Limits.getCpuTime();
startDT = DateTime.now();
average = 0;
Integer numRecs = 0;
for(Account acct :[SELECT Id, MRR__c FROM Account WHERE Account_Status__c = 'Active' AND RecordType.Name = 'Billing']){
    average += acct.MRR__c == null ? 0 : acct.MRR__c;
    numRecs++;
}
average = average/numRecs;

endDT = DateTime.now();
endTime = Limits.getCpuTime();

system.debug(System.LoggingLevel.INFO, 'time to execute: ' + (endTime - startTime));
system.debug(System.LoggingLevel.INFO, 'execution in ms: ' + (endDT.getTime() - startDT.getTime()));

In 5 separate runs through anonymous apex, with profile levels set to 'NONE' except for the Apex and Profiling categories (set to 'INFO'). My query ended up pulling 7,167 records total. I ran my code once before taking measurements to eliminate variance from any compile caching Salesforce might do for anon apex. My results were as follows:

Aggregate function ( AVG() )

  • Run 1
    • 6 CPU units
    • 421 milliseconds
  • Run 2
    • 5 CPU units
    • 519 milliseconds
  • Run 3
    • 6 CPU units
    • 387 milliseconds
  • Run 4
    • 5 CPU units
    • 395 milliseconds
  • Run 5
    • 6 CPU units
    • 632 milliseconds

SOQL for loop + apex

  • Run 1
    • 658 CPU units
    • 1635 milliseconds
  • Run 2
    • 649 CPU Units
    • 1427 milliseconds
  • Run 3
    • 704 CPU Units
    • 1442 milliseconds
  • Run 4
    • 653 CPU Units
    • 1731 milliseconds
  • Run 5
    • 654 CPU Units
    • 1468 milliseconds

It's no contest. SOQL Aggregate functions are faster to execute, and put 2 orders of magnitude less stress on your CPU governor limit. I will note that the following variant that doesn't use a SOQL for loop is measurably better (~513 CPU time, ~1270 milliseconds) in my particular case, but it's still no contest.

startTime = Limits.getCpuTime();
startDT = DateTime.now();
average = 0;
Integer numRecs = 0;
List<Account> accts = [SELECT Id, MRR__c FROM Account WHERE Account_Status__c = 'Active' AND RecordType.Name = 'Billing'];
for(Account acct :accts){
    average += acct.MRR__c == null ? 0 : acct.MRR__c;
    //numRecs++;
}
average = average/accts.size();

endDT = DateTime.now();
endTime = Limits.getCpuTime();
| improve this answer | |
2

Yeah it is always feasible to use aggregate query specially if you have large data set. If you are using normal SOQL query then you can hit following limitations like CPU Time limit, Heap Size. Also If you are not making any DML then you can also use Read Only tag on VF page which again increases the limits.

| improve this answer | |

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.