Salesforce coding lessons for the 99%
Finally, Apex tutorials for point-and-click admins! Written by a self-taught Google engineer.
  • Beginner Tutorials
    • Apex
    • Certifications
    • Career Info
    • Technical Architect
    • Visualforce
    • Videos
  • Apex Academy
  • Success Stories
  • About Me
  • Misc
    • Mailbag
    • Challenges
    • Links
    • Login to my Org
Follow @dvdkliuor SUBSCRIBE!

The #1 most important Governor Limit

November 23, 2013

Preface: this post is part of the Bulkify Your Code series.

If Apex could be defined by a single sentence, it would be this Governor Limit error message:
Too many SOQL queries: 101

Every Apex developer, without exception, must face this error. This is the monster that you must slay in order to become an adult developer. It’s so important that I’ve dedicated an entire chapter to it.

Know that this limit doesn’t get its own chapter because it’s complicated – it just occurs so often!!
In this chapter I’ll teach you why you’re getting this error and how you can beat it!

And what better way to do this than to create a “broken” trigger and fix it!

// Email-to-Case always defaults the creator - this fixes that!
trigger FixCreator on Case (before insert) {
for (Case newCase : Trigger.new) {
if (newCase.SuppliedEmail != null) {
User trueCreator = [SELECT Id FROM User WHERE Email = :newCase.SuppliedEmail ORDER BY LastLoginDate DESC LIMIT 1];
newCase.CreatedById = trueCreator.Id; } } }

(Ask Salesforce for the Create Audit Fields permission in order to set system fields like CreatedById!)

Even though you won’t get any errors when you save this trigger, you will get the 101 SOQL query limit if your trigger runs under certain conditions.

Try to figure out why this limit can happen with this trigger – we’ll talk about how to get around it next! I’ve given some hints by highlighting important lines in green!

Next post: Bulkifying your code!

24 Comments
Rudi
March 9, 2016 @ 8:38 pm

Hi David,

Is it possible to turn on Create Audit Fields on Developer Edition? I keep clicking on contact us, but it takes prompts me to login just keeps going on a loop. Any direction would be helpful.

Reply
    David Liu
    March 10, 2016 @ 6:10 pm

    Probably not possible, I doubt Salesforce will support tickets for non-paying customers!

    Reply
      Robin De Bondt
      May 27, 2016 @ 12:25 am

      No need to contact support any more, this is now a feature that can be turned on in the user interface settings.

      Reply
Mayank Srivastava
February 13, 2015 @ 8:45 am

David,
Why are we assuming that the person who sends an Email will always be a user in our system? I’m asking because as far as I know, in most business cases/companies , the ‘customers or Email senders’ are not a part of your Salesforce User List and they have no log-ins.
If I’m understanding it right, for your code to work, the sender must be a user of the system and multiple such users are linked to the same E-mail ID. Please correct me if I’m wrong.

Reply
    David Liu
    February 13, 2015 @ 9:32 pm

    I think this is very dependent on your org. Every org I’ve worked in uses cases internally only, but that’s not to say that a lot of orgs use it externally as well.

    In any case, it’s more an exercise to understand the code, not the business context behind it ha ha ha.

    Reply
      Mayank Srivastava
      February 14, 2015 @ 4:57 am

      Understood. Thanks!

      Reply
Liz K.
October 5, 2014 @ 5:42 pm

Is there an alternate example to use if one is too impatient to wait to ask salesforce to allow editing of CreatedById? :) i’m in the zone with your blog posts. I guess I could try my own …. Hmmmm

Reply
    David Liu
    October 6, 2014 @ 8:13 pm

    You can use a custom Created_By_ID__c lookup field instead =)

    Reply
Krishan Gopal
September 30, 2014 @ 11:01 pm

Please help to get it resolve:: Error when click on save button

/ Email-to-Case always defaults the creator – this fixes that!
trigger FixCreator on Case (before insert) {
for (Case newCase : Trigger.new) {
if (newCase.SuppliedEmail != null) {
User trueCreator = [SELECT Id FROM User
WHERE Email = :newCase.SuppliedEmail
ORDER BY LastLoginDate DESC
LIMIT 1];
newCase.CreatedById = trueCreator.Id;
}

Error: Compile Error: Field is not writeable: Case.CreatedById at line 8 column 7

Reply
    David Liu
    October 1, 2014 @ 10:22 am

    You need to ask Salesforce to turn on the “Audit System Fields” permission for you!

    Reply
Pankaj Kumar
July 23, 2014 @ 12:28 am

Hi David, This is the best of example and explanation I found on internet about clear cut demarcation of bulkification.Previously, I was always getting stuck @ this point.Excellent post and excellent precised explanation.

Reply
Rajiv Bhatt
April 6, 2014 @ 9:13 am

Hi David,

You are correct that the “Too many SOQLs” error occurs very frequently. No doubt that the code should be written efficiently to avoid this error but just in case you encounter this in future here is something that should be helpful.

One of my colleague shared a nice tip to debug this error message, he asked me to extract all the SOQLs in the debug log, get their execution counts and the objects on which these SOQLs were fired. It was quite a tedious task as he asked me to extract all the SOQLs from debug logs into excel and then I had to use some delimiters and formulas to get the query counts.

Luckily, I managed to find a “One Click” solution for this. I wrote a google chrome extension that does the following :

It helps you extract and analyze the SOQL queries from the debug logs of Salesforce. All you need to do is open up any Debug Log on Salesforce and then on just “One-Click” of the extension it would display the following information:

1. All the SOQL queries along with their execution count i.e. the number of times each query was executed.

2. The extension would also display all the Objects (Standard/Custom) on which these SOQL queries are executed.
For ex: If you have got queries that hit the Account object 5 times, you might want to have a relook at the code and optimize the code in a way that you get all the data from the Account object in single SOQL query (instead of querying it 5 times).

These statistics would help in debugging the error “Too Many SOQLs”. and they would also help you to optimize the performance of your application.

You may get it from : https://chrome.google.com/webstore/detail/soql-extractor-and-analyz/deihalhihjdilndoidoclmhmgllebkfj

Just thought of sharing this you as you might find it helpful. I will appreciated your thoughts and feedback for this one.

Thanks!

Reply
    David Liu
    April 6, 2014 @ 3:06 pm

    Thank you Rajiv, very helpful for those needing to clean up some legacy code!

    Reply
sree376
December 24, 2013 @ 9:01 am

Eagerly waiting for next posts….Finger cross…

Reply
Neha
December 18, 2013 @ 11:27 pm

Hello David,

I will write this below Query above the for loop and retrieve all the newly inserted accounts contacts and then perform the operation in for loop

List contacts = [SELECT Id,BillingStreet,Billingcity,BillingState,BillingCountry from contact c where c.accountid IN Trigger.New)

Is this correct?

Reply
    Sunil
    January 13, 2014 @ 3:26 am

    Neha, Its not correct “List ct = [SELECT Id,BillingStreet,Billingcity,BillingState,BillingCountry from contact c where c.accountid IN Trigger.New]”

    Reply
      David Liu
      January 13, 2014 @ 8:04 pm

      Good job to both of you! Don’t forget to include the “:” before Trigger.new!
      “Using Apex variables in a SOQL query”
      https://www.sfdc99.com/2013/11/04/using-apex-variables-in-a-soql-query/

      [SELECT Id,BillingStreet,Billingcity,BillingState,BillingCountry from contact c where c.accountid IN :Trigger.New];

      I’ve changed up the trigger actually because the new one is a little better at explaining my focus for this chapter (you’ll see in the next few posts!)

      David

      Reply
      David Liu
      January 13, 2014 @ 8:06 pm

      Don’t forget to include the semi-colon (:) before Trigger.new!
      “Using Apex variables in a SOQL query”
      https://www.sfdc99.com/2013/11/04/using-apex-variables-in-a-soql-query/

      [SELECT Id,BillingStreet,Billingcity,BillingState,BillingCountry from contact c where c.accountid IN :Trigger.New];

      Also, I changed the trigger up because the new one does a better job of hitting governor limits!
      David

      Reply
Neha
December 18, 2013 @ 10:55 pm

I think.. we should not write a SOQL Query inside the for loop as the SOQL query executes once for each iteration and may surpass the 100 SOQL queries limit per transaction

Reply
    David Liu
    December 18, 2013 @ 11:20 pm

    Excellent observation Neha =)

    You’re on your way to becoming a great developer!!

    Reply
      Neha
      December 18, 2013 @ 11:28 pm

      Thank you so much :)

      Reply
        Sairam
        July 31, 2015 @ 12:43 am

        How do we achieve this scenario without writing SOQL query inside for loop? is there any way?

        Reply
          David Liu
          July 31, 2015 @ 9:48 am

          Keep reading and you’ll find out!

          Reply
Usha
December 16, 2013 @ 10:40 am

Hey David,
I’m waiting eagerly for your next posts. Your tutorials definitely changed the way I look at apex today. Thank you so very much.

Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *


*

*

Theme: Simple Style by Fimply