Monday, 29 July 2013

Reducing SOQL queries in Triggers by updating on ID

I have recently been battling the SOQL query limit on a large Force.com project I'm working on.

One of the biggest problems we have is with SOQL in Triggers, over and over again, we were using SOQL to load a list of parent/related objects, modifying their contents and then updating them.

Then one day, I realised, we didn't need to do this at all.

Here is an example of a trigger that "passed" a status field from an updated object back to it's parent:

trigger UpdateParent on Child__c (after insert, after update)
{ 
    // get the list of parent object IDs
    List Ids = new List();
    for(Child__c thisChild : Trigger.new) {
         Ids.add(thisChild.ParentRef__c);
    }

    // Load up the list of parent objects and map them out for altering and updating
    List parentList = [SELECT Id FROM Parent__c WHERE Id IN :Ids];

    Map parentMap = new Map();
    for(Parent__c thisParent : parentList) {
        parentMap.put(thisParent.Id, thisParent);
    }

    // This is our list of parents to finally update
    List parentsToSave = new List();
    
    // Finally we're ready to go through each child in the trigger and update it's parent

    for(Child__c thisChild : Trigger.new)
    {
        Parent__c par = parentMap.get(thisChild.ParentRef__c);
        par.Child_Status__c = thisChild.Status__c;
        parentsToSave.add(par);
    }

    // update the parents.
    update parentsToSave;

}


That's actually pretty hefty, it's a lot of maps and lists, and the SOQL query right in the middle there, just to update one field on a related object. I think this technique is a bastardisation of the Force.com Fundamentals teachings, which has emphasis on batchification, and in it's example, probably has a lot more cause to go through this process. For me though, this could be simplified by taking advantage of a simple Force.com fact:

If you create an instance of an sObject, and assign a valid record ID to it, you can update or upsert it exactly as if it was originally loaded to memory.

This means, that as we already have our parent object IDs in a reference field, we don't need to LOAD them, we can just conjure up new ones, update the relevant field, and update them. That makes the above trigger look like this:

trigger UpdateParent on Child__c (after insert, after update)
{
    // This is our list of parents to finally update
    List parentsToSave = new List();
    
    // Now we can just go through each child in the trigger "instantiate" it's parent, update and, update!

    for(Child__c thisChild : Trigger.new)
    {
        Parent__c par = new Parent__c();
        par.Id = thisChild.ParentRef__c;
        par.Child_Status__c = thisChild.Status__c;
        parentsToSave.add(par);
    }

    // update the parents.
    update parentsToSave;    
}



I can literally feel my governor limits squeeling with joy already!! No SOQL, half the script statements, a quarter of the lists and maps. Fantastic.

5 comments:

  1. Brilliant, I'm going to start integrating this tomorrow!

    ReplyDelete
  2. Brilliant, I'm going to start integrating this tomorrow!

    ReplyDelete
    Replies
    1. Hi Nathan, how did this go for you? Did you get your SOQL calls down? I've just revisited my own blog to remind myself of this process. So, so often you find yourself loading objects from IDs just to update and save them, without realising you can totally avoid that.

      Delete
  3. If we update more than one contact for the same account at a time. Then it will show duplicate Id error...........

    ReplyDelete
    Replies
    1. Yes, you're quite right. I think this is probably a weakness in both scenarios given above (original and improved) so you would have to look at using a Map<> or logically deduping your lists before insert. Thank you for commenting :)

      Delete