2

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 :)

2
  • I believe this is a duplicate of the linked questions. If not, let me know and I'll be happy to reopen.
    – sfdcfox
    Aug 12, 2022 at 14:53
  • The linked questions are not the same question as mine. If you could please reopen I would appreciate it :)
    – joe jung
    Aug 12, 2022 at 17:59

2 Answers 2

4
+50

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.

-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.

2
  • 1
    This seems to be more methodology than an answer to the question itself.
    – Derek F
    Jun 4, 2023 at 18:59
  • Hey @DerekF, correct I thought rather than running the code in my org which has my data. It would be better if Joe Jung can run it in personal org with data Jun 5, 2023 at 6:19

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .