2

I need to fetch orders by account. Since orders are children of account I can use the aggregation query parent-to-child.

Query to be used:

SELECT Id, (SELECT Id FROM Orders) FROM Account

I can think of two ways of accomplishing the desired data (orders by account):

1) using SOQL loop query

Map<Id, List<Order>> ordersByAccountIdMap = new Map<Id, List<Order>>();

for (Account acc : [SELECT Id, (SELECT Id FROM Orders) FROM Account]) {
    ordersByAccountIdMap.put(acc.Id, acc.Orders);
}

Id accountId = '0018E00000uM2xM';
List<Order> orderList = ordersByAccountIdMap.get(accountId);

2) assignment of SOQL query to map

Map<Id, Account> ordersByAccountIdMap = new Map<Id, Account>([SELECT Id, (SELECT Id FROM Orders) FROM Account]);

Id accountId = '0018E00000uM2xM';
List<Order> orderList = ordersByAccountIdMap.get(accountId).Orders;

Question

1) Which approach is faster and take less resources?

  • On top of my head, 2nd would be faster as you are not running any loop and the conversion of list to Map happens internally so it is supposed to be faster than custom apex code. Intersting, have you tried executing this in anon window and see performance CPU time performance and heap used? – Pranay Jaiswal Jul 12 '18 at 9:13
4

Using the Map constructor is idiomatic in Apex and so is a good way to go as experienced Apex developers will immediately understand its purpose. It also saves a bit of tedious typing.

A loop still has to execute behind the scenes and it is possible/likely that that loop is a bit faster. But the time taken will be dominated by the query execution and transfer of the query records.

(Both parts of your query are likely to hit governor limits.)

Bottom line: use the Map constructor but as always be careful about the query.

  • The only downside I see is we cannot use SOQL For Loop returning List, with the 2nd approach. – Pranay Jaiswal Jul 12 '18 at 9:39
  • 1
    @PranayJaiswal Not having a variable for the list is a good thing if it is not required... – Keith C Jul 12 '18 at 9:47
2

Questions like this tend to fall into the "premature optimization" category.

In most cases, the difference between the two approaches will be negligible. If you are running into the heap space or CPU time limits, there are likely bigger issues in your codebase that should be addressed first.

What SOQL For-Loops are good for

The big benefits of a SOQL for-loop is the "efficient chunking" that reduces the number of records in memory (i.e. the heap) at any given time inside the loop. It also causes the records to be scoped to the for loop, which allows the allocated memory to be given back to the heap any time after the loop has finished executing.

Since you're putting every record into a map anyway, there's likely no benefit (as far as limits are concerned) to using a SOQL for-loop. The story isn't quite that simple, though.

Fetching subquery results is a special case

Work with parent-child subqueries often enough, and you'll eventually run into an error

Aggregate query has too many rows for direct assignment, use FOR loop

We still don't have clear documentation on when this error will pop up (the threshold seems to be somewhere between 200 and 600 total child records in your overall query), but it seems to be related to internal calls to queryMore() by Salesforce when you try to access a child record collection.

That means that safest, most performant approach here is something like this

List<Account> myAccounts = [SELECT Id, (SELECT Id FROM Orders) FROM Account];
Map<Id, List<Order>> ordersByAccount = new Map<Id, List<Order>>();

for(Account acct :myAccounts){
    // Using a dedicated variable instead of accessing a field on an object
    //   is a small optimization.
    // Saves an immeasurably small amount of CPU time if you use this variable
    //   at least twice inside a for loop
    // Seriously, don't bother going back to old code and adding this.
    // Your time would be better spent improving other aspects of your code.
    Id myAcctId = acct.Id;
    for(Order ord :myAccounts.Orders){
        // General map populating pattern
        // Reduces amount of typing just a little bit, and keeps
        //   common logic (adding stuff to the list) outside of the if/else
        if(!ordersByAccount.containsKey(myAcctId)){
            ordersByAccount.put(myAcctId, new List<Order>());
        }
        ordersByAccount.get(myAcctId).add(ord);
    }
}

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.