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!

Bulkify your code by combining SOQL queries

January 20, 2014

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

Best advice I’ll ever give you: never ever have a SOQL query inside a loop!

Having a SOQL query inside a loop is a major breach of the unwritten code of Apex developers. It’s like wearing socks and sandals at the same time. Don’t ever wear socks with sandals, trust me on this one!

Why can’t we have SOQL queries inside loops?
Simple. The #1 most common governor limit restricts you to 100 SOQL queries. Loops, especially those iterating over Trigger.new, will easily run 200+ queries and push you across the limit.

How do we avoid using SOQL queries inside loops?
Combine your queries into one! There’s always a way to do this for any trigger you write!
Let’s look at this chapter’s trigger, which can potentially run 200 SOQL queries, and combine them.

How to combine SOQL queries:

  1. Create a Set of all potential values you need to query against.
    – In this chapter’s trigger, this means every possible value of newCase.SuppliedEmail
  2. Query against all records needed in Step 1 using a single SOQL query
    – Always do this before entering the loop!

Now we apply the above steps to this chapter’s trigger:

trigger FixCreator on Case (before insert) {

  // Step 1: Create a set of all values to query
Set<String> allEmails = new Set<String>();
for (Case newCase : Trigger.new) { if (newCase.SuppliedEmail != null) {
allEmails.add(newCase.SuppliedEmail);
} } // Step 2: Query for all the records in Step 1 List<User> potentialUsers = [SELECT Id, Email FROM User
WHERE Email IN :allEmails];
// Step 3: Associate each record with the correct query result for (Case newCase : Trigger.new) { // We'll cover this in the next post! } }

Now that we’ve combined our queries into one, we no longer have to worry about hitting the 100 SOQL query governor limit.

We have a new problem now however. Before combined queries, it was easy associating the correct Case creator to each Case because we simply did a brand new SOQL query and took a single result. Now however with combined queries, we have to choose the correct Case creator for each Case out of our combined list of many potential creators!

We’ll solve this dilemma and close the book on this chapter in the next post!

Next post: Use Maps to navigate across Lists!

35 Comments
Ritu
June 6, 2019 @ 8:31 am

Hi david
I wrote the following code in a method.
public static void Updateproductprice(CPK_Price_Floor__c[] prfloor){
Set targetprice = new Set();
Set floorprice = new Set();
Set product = new Set();
for (CPK_Price_Floor__c newprice: prfloor){
if (newprice.CPK_Product__c != null){
product.add(newprice.CPK_Product__c);
targetprice.add(newprice.CPK_Target_Price__c);
floorprice.add(newprice.CPK_Floor_Price__c);

}
}
List prodmaster = [select ID, CPK_Target_Price__c,CPK_Floor_Price__c from Product2 where Id IN: product];
for (Product2 prodrecord :prodmaster){
prodrecord.CPK_Target_Price__c = decimal.valueOf(targetprice);
prodrecord.CPK_Floor_Price__c = decimal.valueof(floorprice);
}
update prodmaster;
}

Issue happens on the following 2 lines
prodrecord.CPK_Target_Price__c = decimal.valueOf(targetprice);
prodrecord.CPK_Floor_Price__c = decimal.valueof(floorprice);
}
I get the message:
Method does not exist or incorrect signature: void valueOf(Set) from the type Decimal
I tried (Decimal)(Targetproce); Integer.valueof (Targetprice) gets rid of the compilation error but gives me an execution error.
What am I doing wrong?

Reply
Prabhu
June 19, 2017 @ 6:54 am

Hi David,
Thanks a lot for the great posts. It’s easy to understand…

Reply
    David Liu
    June 19, 2017 @ 8:50 pm

    Thanks!!

    Reply
sucharita
March 24, 2017 @ 6:19 am

Hey David!

First of all nice explanation about bulkify.
I have one question to ask Why you’ve created Set in the Step-1 and not List. Is it to prevent duplicate email values?
If not then What if we use List over Set in Step-1?

Thanks!

Reply
    David Liu
    March 26, 2017 @ 1:27 pm

    You’re exactly right – using Sets you don’t need to worry about dupes. No point over-engineering!

    Reply
Sunil Nandipati
September 15, 2014 @ 3:02 pm

In the first piece of the code
// Step 1: Create a set of all values to query
Set allEmails = new Set();
for (Case newCase : Trigger.new) {
if (newCase.SuppliedEmail != null) {
allEmails.add(newCase.SuppliedEmail);
}
}

.. does this handle duplicate emails? What happens when you add duplicate keys to the Set? Would it error out?

Reply
    Sand
    January 12, 2015 @ 4:23 am

    It will just simply not add that value to the set(i.e if it is a duplicate)

    Reply
Sandeep
April 23, 2014 @ 3:29 am

Doubt cleared after reading the next post. Its like the missing piece to the puzzle. It all fits so well ,.. Haha
Thanks again.
Sandeep

Reply
    David Liu
    April 23, 2014 @ 9:43 pm

    Woohooo!!!!!! Great job learning Sandeep!

    Reply
Sandeep
April 23, 2014 @ 1:34 am

Hi David,

Thanks again for all your help.
This might be a stupid doubt, but I asking anyway

Could you please explain this part in detail” it was easy associating the correct Case creator to each Case because we simply did a brand new SOQL query and took a single result”.
In such a case how would the SOQL query be as opposed to the combined one?

To make this doubt more clear, I am not clearly able to understand when you say queries have to be combined into one.
Does this mean every Trigger has to have only one master SOQL query as opposed to many small soql queries.

Thanks,
Sandeep

Reply
Mthobisi
April 16, 2014 @ 5:39 am

Hi David

I’m new to Apex and I have found your site very helpful but currently I am finding myself in a serious bind with SOQL left outer join ..

I have two custom objects with a Parent-child relationship, name of the objects.. Applicant__c (Parent), Application_Review__c (Child).

SELECT Applicant__c.Id, Applicant__c.Name, (SELECT Application_Review__c.Subject__c, Application_Review__c.Final_Score__c FROM Applicant__c.Application_Review__c ) FROM Applicant__c

The FROM statement of the inner query throws a “Didn’t understand relationship” error, even if i write it like this Applicant__r.Application_Review__c.

What’s more confusing is that if I run this query : SELECT Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.Contacts) FROM Account. It runs fine and I got the idea from it.

the end result basically is to have a VF page which displays a list of applicants and have their final score of a particular subject entered. but right now I can’t even get the query working.

NB: the result scores will be updated or inserted on the Application_Review__c object.

Thanks for taking your and going through my problem.

Reply
    David Liu
    April 16, 2014 @ 10:22 pm

    Hahaha, I know the feeling!

    You can find the exact syntax by referencing the picture here:
    https://www.sfdc99.com/2013/06/24/example-how-to-write-a-cross-object-soql-query-part-2/

    It’s probably Application_Review__r.

    You can also use a tool called Workbench as well!
    https://workbench.developerforce.com/login.php

    David

    Reply
      Mthobisi
      April 23, 2014 @ 12:03 am

      Hi David

      Sorry for such a late reply, I had a looong easter weekend. I did go through the link you sent which was very helpful, it turns out that the child relationship name is named application_assessments__r which was the original name of the application_review__c object before it got changed.

      but now the issue is trying to display the inner select field on the VF page table i keep getting the following error.

      Save error: Could not resolve the entity from value binding ‘{!lstApp.Applicant_Assessments__r.Review_3_Total_Score__c}’. can only be used with SObjects, or objects that are Visualforce field component resolvable.

      Name displays pretty fine but not the score,

      One last question, one the requirements is to display picklist which will contain subject how do i dynamically populate the selectOption using the same query as above or the best way to approach it? I have seen examples on how to do it but those mainly focus on just the getting the picklist values none of the queries are anything like mine.

      in a query like mine what’s the best way of doing that, please note that the values will be queried using the inner select.

      I will continue to try and find a solution to my problems while I wait for your reply

      Thanks :-)

      Reply
        David Liu
        April 23, 2014 @ 9:36 pm

        lstApp.Applicant_Assessments__r refers to a list of records, while you are trying to access just one of them!

        Two options:
        1. Change to lstApp.Applicant_Assessments__r[0].Review_3_Total_Score__c for just the first record OR
        2. Use apex:repeat or a similar table to have a row for each record!

        You can use apex:repeat to do the selectOption thing as well =)

        Go get em!!!
        David

        Reply
          Mthobisi
          April 25, 2014 @ 3:05 am

          So I tried your suggestion, worked with displaying…. but boy did I struggle with the save method but I just finished it and everything works like a dream. thanks to you and the info you shared with us on this site. :-)

          BE WARNED: I WILL BE BOTHERING YOU AGAIN IN THE NEAR FUTURE LOL!!!

          ON TO THE NEXT VF PAGE NOW!!!

          Reply
            David Liu
            April 25, 2014 @ 9:25 pm

            woohoo great job!!!

            Reply
Peter Noges
April 6, 2014 @ 10:31 am

your next post clarifies my question. In the next post (https://www.sfdc99.com/2014/01/25/use-maps-navigate-across-lists/) you say “Your big query happens outside of any loops”

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

    ^_^

    Reply
Peter Noges
April 6, 2014 @ 10:18 am

Hey David,

You say “Always do this before entering the loop!”. I’m caught up on the word ‘before’ in that sentence. Could it have read “before or after” instead of “before”? Additional context here https://gust.box.com/s/b7es13wr00ab8aalj8ul

Reply
ajay
March 13, 2014 @ 6:05 am

hii,David Liu sir dis is ajay kumar,i have some doubts in triggers,in which senario i wil use before and after,in before trigger also you used in one example trigger.new,please explain me or mail me entire trigger notes……..

Reply
    David Liu
    March 13, 2014 @ 9:55 pm

    Hey Ajay,

    Have you checked out this post yet? I bet it’ll help!
    https://www.sfdc99.com/2014/01/25/use-vs-triggers/

    David

    Reply
Dan
January 27, 2014 @ 12:26 am

Hey David,

How would one go about this if they wanted to specify two filter criteria in their SOQL query? My understanding is that sets only contain one element. Could you use maps in place of sets?

Reply
    David Liu
    January 27, 2014 @ 9:51 pm

    This post goes pretty crazy with multiple filter criteria – let me know if this answers your question!
    https://www.sfdc99.com/2013/06/07/example-how-to-write-a-simple-soql-query/

    Sets can have infinite elements, as long as each one is unique! IE if you have a set of email addresses, each email address can only appear once!

    Reply
      Dan
      January 31, 2014 @ 12:53 am

      Sorry, I should have been more clear.

      Let’s say I want to follow the same process described but with two filter criteria (for example, if I want my SOQL query to be based on email AND last name). I first have to create a set from the records that are being inserted, but a set won’t contain paired information, will it?

      How can I go about running the more advanced SOQL query using information from the inserted records?

      Reply
        Dan
        January 31, 2014 @ 12:54 am

        >< sorry for my poor use of HTML

        Reply
        Dan
        January 31, 2014 @ 7:44 am

        The html should read that I want to follow the process in this post: https://www.sfdc99.com/2014/01/25/use-maps-navigate-across-lists/

        The same process you use to “bulkify your code,” only this time we’re collecting two pieces of information from the new records, and using them in an SOQL query.

        Reply
        David Liu
        January 31, 2014 @ 8:07 pm

        Try something like this!

        List<Contact> contacts = [SELECT Id FROM Contact WHERE Email IN :emailSet AND LastName IN :nameSet];

        Reply
Andrew Bartels
January 25, 2014 @ 7:12 am

As usual another terrific and well explained post. This blog is well on its way to becoming the defacto beginners guide to learning how to write SFDC APEX. Thank you David.

Reply
    David Liu
    January 25, 2014 @ 11:48 am

    My pleasure Andrew, loving it!

    Reply
Malachi
January 23, 2014 @ 1:16 pm

Thanks for the tutorials, they are awesome for a salesforce-but-not-programming newbie such as myself. Do you have a timeframe of when certain chapters will be done? I guess I got a little bit spoiled since I started chapter 1 this morning and now I’m already ready for the next part of this chapter!

Reply
    David Liu
    January 23, 2014 @ 9:30 pm

    You’re making great progress Malachi!!!

    I try very hard to get 1 – 2 posts out a week, too slow for a speedster like you unfortunately!!!

    Here are two tasks for you in the meantime =)
    1. Write a trigger that automatically changes a Lead status to “In Progress” when the first task is created on it
    2. Figure out how to use Maps to finish chapter 5!

    Good luck =)
    David

    Reply
      sinanbunni
      August 14, 2015 @ 7:12 am

      Hi David,

      how do I know that it is the ‘first task’ on the lead…
      Tried to think about it and come up with something like below

      List tasks = [Select count() from Task where CreatedDate = TODAY]

      I am sure this is incorrect and there should be a simpler answer.

      Thank you for your help, Sinan

      Reply
        David Liu
        August 17, 2015 @ 10:39 am

        Order it by CreatedDate =)

        Then take the first one in the list ie tasks.get(0);

        Reply
Anurag Dixit
January 21, 2014 @ 4:02 am

Hey Devid !!

I am became your fan after reading your posts on this site…. gr8 technique to explain the topics… waiting for next page.
Thanks alot

Reply
    David Liu
    January 21, 2014 @ 8:57 pm

    My pleasure Anurag!

    Reply

Leave a Reply Cancel reply

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


*

*

Theme: Simple Style by Fimply