Saturday 13 October 2018

All Governor Limits are Created Equal

All Governor Limits are Created Equal

Soql

Introduction

Not all Salesforce governor limits inspire the same fear in developers. Top of the pile are DML statements and SOQL queries, followed closely by heap size, while the rest are relegated to afterthought status, typically only thought about when they breach. There’s a good reason for this - the limits that we wrestle with most often bubble to the top of our thoughts when designing a solution. Those that we rarely hit get scant attention, probably because on some level we assume that if we aren’t breaching these limits regularly, we must have some kind of superpower to write code that is inherently defensive against it. Rather than what it probably is - either the limit is very generous or dumb luck.

This can lead to code being written that is skewed to defending against a couple of limits, but will actually struggle to scale due to the lack of consideration for all limits. To set expectation, the example that follows is contrived - a real world example would require a lot more code and shift the focus away from the point I’m trying to make. 

The Example

For some reason, I want to create two lists in my Apex class - one that contains all leads in the system where the last name starts with the letter ‘A’ and another list containing all the rest. Because I’m scared of burning SOQL queries, I query all the leads and process the results:

List<Lead> leads=[select id, LastName from Lead];
List<Lead> a=new List<Lead>();
List<Lead> btoz=new List<Lead>();
for (Lead ld : leads)
{
    String lastNameChar1=ld.LastName.toLowerCase().substring(0,1);
    if (lastNameChar1=='a')
    {
        a.add(ld);
    }
    else 
    {
        btoz.add(ld);
    }
}

System.debug('A size = ' + a.size());
System.debug('btoz size = ' + btoz.size());

The output of this shows that I’ve succeeded in my mission of hoarding as many SOQL queries as I can for later processing:

Screen Shot 2018 10 13 at 16 20 14

But look towards the bottom of the screenshot - while I’ve only used 1% of my SOQL queries, I’ve gone through 7% of my CPU time limit. Depending on what else needs happens in this transaction, I might have created a problem now or in the future. But I don’t care, as I’ve satisfied myself requirement of minimising SOQL queries.

If I fixate a bit less on the SOQL query limit, I can create the same lists in a couple of lines of code but using an additional query:

List<Lead> a=[select id, LastName from Lead where LastName like 'a%'];
List<Lead> btoz=[select id, LastName from Lead where ID not in :a];
System.debug('A size = ' + a.size());
System.debug('btoz size = ' + btoz.size());

because the CPU time doesn’t include time spent in the database, I don’t consume any of that limit:

Screen Shot 2018 10 13 at 16 23 59

Of course I have consumed another SOQL query though, which might create a problem now or in the future. There’s obviously a trade-off here and fixating on minimising the CPU impact and ignoring the impact of additional SOQL queries is equally likely to cause problems.

Conclusion

When designing solutions, take all limits into account. Try out various approaches and see what the impact of the trade-offs is, and use multiple runs with the same data to figure out the impact on the CPU limit, as my experience is that this can vary quite a bit. There’s no silver bullet when it comes to limits, but spreading the load across all of them should help to stretch what you can achieve in a single transaction. Obviously this means the design time takes a bit longer, but there’s an old saying that programming is thinking not typing, and I find this to be particularly true when creating Salesforce applications that have to perform and scale for years. The more time you spend thinking, the less time you’ll spend trying to fix production bugs when you hit the volumes that everybody was convinced would never happen.

 

 

5 comments:

  1. Read your debug logs, people.

    ReplyDelete
  2. In your second snippet, query #2 could potentially be non-selective though, right? And Salesforce could potentially put the kibosh on it if it so desires, methinks. I think I'm not making that up? (Though I do get your point.)

    And thanks again for the help the other day on SSE... And the help you've given before that... and I think before that..

    (comment issue appears to just be me, btw.. wacky chrome issue... cookies and whatnot probably..)

    ReplyDelete
  3. Hi Keir, first of all thanks for sharing the article which I love. I am wondering if in the second solution, are you introducing some future issues as using a negative operator, will produce a non-selective query?
    Again, thanks for sharing your experience and knowledge.

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete