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.
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:
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();
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.
