If you are a salesforce admin or developer using salesforce org, one of the dreaded governor limit exception is the Soql limit exception which gets thrown. This happens during deployments, development or if you have created process builder flows or created some new workflows on standard objects like accounts, opportunities or contacts. This post will  help you to identify root causes and steps you can do to proactively to prevent them from happening.

Root Cause of the issue

Salesforce mandates that in a given execution context, you are not allowed to execute more than 100 SOQLs which are queries on objects . Now execution context means different things for different people but you can think this as a box which salesforce provides you to operate as soon as you click the edit or new button on a page.

Now if your salesforce org has  a trigger built which is doing loops over some objects, a process builder which does some update on the object, a workflow which also does an object update, this is a classic situation that you would be hitting the governor limit soon. Now the major reason on why this happens is because there are some meta data components like the process builder, workflow or trigger which is called again and again hitting more soqls and causing the error to happen.

How do we prevent this from happening? Here are some pointers for you to check.

1. Reduce unwanted sqls

The first area you would want to start is the trigger code. If you have triggers which have loops meaning querying objects and have soql queries inside the for loop, you would want to eliminate it as much as possible. As an example, if you have a search page where you search products based on certain search criteria like product name, availability etc, you would be querying all the products which match the criteria and loop through each product to find the availability. This works great for a few products but the more inventory, it causes more soqls and it hits the 100 soql limit. So best practice is to do the following

a. Use maps or lists and store the product results before you make the call to products again and again.

2. Avoid triggers from firing again and again

This is another cause of the problem. I have seen orgs where there are more than 4 triggers per object and each trigger is caused by record insert or update. So in a scenario where you have a requirement where an account has a parent account and if you want to update all the children accounts based on the parent accounts field, the triggers update one child account and this in turn causes the child account to fire the trigger again. So if you have say 5 to 10 child accounts per parent account, your trigger will fire 5 to 10 times for every child account and this increases the soql limit.

a. You can create static variables on your apex classes and fire the trigger only once per initiation.

b. Have triggers fire only on field changes rather than every record insert or update.

c. If you have more than 3 triggers per object, it is time to revisit all the triggers and consolidate them into one trigger class and have a helper class to perform all the code for you.

The below is an example of an apex class which can be used to prevent triggers to be fired again and again on child record updates.

public class CheckRecursive 
{
    private static boolean run = true;
    public static boolean runOnce()
    {
    
        if(run)
    	{
            system.debug(run+' =run called first');
             run=false;
             return true;
        }
        else
        {
            	system.debug(run+' =run called else');
           return run;
        }
   }
}

 Sample Trigger
trigger updateTrigger on anyObject(after update) {

    if(checkRecursive.runOnce())
    {
    //write your code here            
    }

}

3. Avoid process builders to fire again and again.

This is another classic example which happens. We might have scenarios in your major objects like Accounts or opportunities where you might have 2 to 3 triggers on the object and 2 or 3 process builders which perform data updates on them. So in that scenario, the process builder will make the opportunity to be updated which in turn fires the trigger and which in turn fires the process builder again causing a recursive loop. How do you avoid it?

a. Ensure your process builder is fired only on a criteria like a field is changed.

b. Ensure that there is a line of separation between triggers and process builders. The criteria s for process builders and triggers should be mutually exclusive and never overlap which will cause both of them not to fire each other.

Below is an example of a process builder criteria.

So following the above 3 tactics, you should be able to avoid the dreaded soql limit issue. Here is the key takeaways for you to avoid soql limits.

a. If you have more than 4 to 5 triggers per object, this is one of the cause of soql limit as they would fire unwanted soqls.

b. Use collection objects like maps or arraylists in code to store the search results and use them for looping instead of querying objects again and again.

c. Have criteria unique for process builder and triggers so that they dont fire each other continuously.

As always feel free to post your comments below or email me at buyan@eigenx.com for further questions.