SOQL query summation aggregation faster than a regular loop

Question

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?

sfdcfox · Accepted Answer · 2018-07-11 00:05:11Z

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.

Stack Exchange Network

SOQL query summation aggregation faster than a regular loop

1 Answer 1

You must log in to answer this question.

Linked

Hot Network Questions

SOQL query summation aggregation faster than a regular loop

1 Answer 1

You must log in to answer this question.

Linked

Related

Hot Network Questions