APEX - Loop and SOQL - Performance Question (Time and Space Complexity)

Question

Hello Salesforce Devs!

I'm just curious to know which one of the below has better performance when looking up child records.

For example, finding number of cases on an account:

#1: List<Account> acctList = [SELECT ID,(SELECT ID FROM Cases) FROM Account LIMIT 1];
    Integer count = 0;
    for(Account acct : acctList) {
        count = acct.cases.size();
    }
    System.debug(count);

#2: List<Account> acctList = [SELECT ID FROM Account LIMIT 1];
    Set<Id> acctIds = new Set<Id>();
    for(Account acct : acctList) {
        acctIds.add(acct.Id);
    }
    List<Case> caseList = [SELECT ID FROM Case WHERE accountId in: acctIds];
    Integer count = caseList.size();
    System.debug(count);

My gut tells me that #2 is faster because it has O(n), but since I don't know how #1 works in the background I'm not 100%. In case of #1, would the time complexity to search for child object records come out to O(n^2)?

In terms of space complexity, #1 would be optimal since it uses only one SOQL and using less query matters in Salesforce world.

By considering both time and space complexity, which way would you construct your code if they are not completely the same? Thanks for your time reading/answering this in advance :)

Answer 1

Sadly enough this question itself is too academical for this stackexchange probably but I'll go with real life + best practices response.

So the answer is... number 1. Thank you good bye.

But seriously: number 2 is actually O(n) + 1. Number 1 is O(n). How come its O(n)^2 according to you? It uses "pointers" (List implementation in Apex it's ArrayList. Because of that there is possibility to check end of array and determine size of it without going through it) so there isn't any additional iteration.

Additionally: Theoretically you should have only 10k child Cases max otherwise you'll encounter problems with Data Skew. Because of that Query with Inner Query would be super fast in real world. It would be always faster then using two separate queries (nevermind the loop in between).

In the end you'll always have bigger problem with amount of queries than performance of such problems.

Answer 2

Interesting question, generally in DSA practice we use time methods between snippets of code to compare performance apart from pen paper Big O notation (comparing using time methods will not be 100% reliable in some cases).

In this case, also you utilize the below methods

Long dt1Long = DateTime.now().getTime();
// Your snippet
Long dt2Long = DateTime.now().getTime();
System.debug('Seconds ' + ((dt2Long - dt1Long))/ 1000);

You can fire multiple times single snippets independently and average out the seconds you are getting and compare the same.

Make sure there is not much difference in data volume, fields, etc. between both the snippets you are comparing.