5

I am wondering if a summation SOQL query aggregation is faster than adding all values on a regular loop. For example:

SOQL query aggregation:

SELECT SUM(TotalAmount) FROM Order WHERE AccountId = '0018E00000uM2xM'

using a regular loop for the summation of totalAmount of all orders with account id 0018E00000uM2xM:

public Decimal totalAmountSummation(List<Order> orderList){
     Decimal totalAmount = 0.0;

     for(Order o: orderList){
         totalAmount += o.TotalAmount;
     }

     return totalAmount;
}
  • which approach is faster?
  • which approach is the best practice?
6

which approach is faster?

The aggregate query is faster; the database can sum far faster than Apex Code can. This is particularly true the closer you get to the 50,000 query row limit. The reason why is that the database doesn't have to check governor limits or anything else: it runs at full speed and can quickly sum up the values.

which approach is the best practice?

Use the aggregate result if you don't need the individual records later. This reduces both heap size and CPU time significantly (basically, O(1) time and space versus O(n) time and space).


As an example, looping over 50,000 rows of data and summing takes an extra 300 milliseconds (about 1/3 of a second), but the database can do that while retrieving the records, resulting in a savings of 300 milliseconds minimum.

Additionally, if you were to load 50,000 rows of data, that would require at minimum 2,900,000 bytes of heap (if my calculations are correct), while the AggregateResult would use closer to 40 bytes.

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.