Salesforce Apex Too many SOQL queries: 101

System.LimitException: Too many SOQL queries: 101

Overview

This Exception is thrown when our code goes over the 100 SOQL queries  (Select, Insert, Update, Upsert, Delete) in a single apex transaction. That governor limit is shared between all triggers executed. This means it’s a counter of SOQLs quieres per transaction increased differently by each trigger executed. Therefore we can say that the “Too Many SOQL” Exception it’s a “Shared blame issue”.

Most common causes that I have seen are due to:

• „Triggers are not bulkified.
• Tigger’s Loops.
• Features are not grouped efficiently. 

Since it’s a “Shared blame issue” the exception can be thrown at any place. Maybe not in the trigger or class which it’s contributing the most to the counter. Therefore, to fix it we have to narrow down and validate couple of things.

1. Verify that your triggers are bulkified.

A Bulkified Code, means the code was adapted so triggers can support big number of records been manipulated (Created, Edited, Deleted) at the same time. Particularly SOQLs operations must be avoided in loops. Data should be grouped using collections (List, Sets, Maps, etc.) and processed together “in a bulk”.

In this page there is a good explanation about how to bulkify your Code:

http://www.sfdc99.com/2014/01/18/bulkifying-code/  

2. Check for Tigger’s loops.

This Image that you work in an organization where you have multiple requests and changes over the same group of related objects. Where updating child record can cause parent update and vice versa.  For instance imagine the following requirements came from different departments:


A) First, Executive department needs that all Contacts Status are updated when the Account Status it’s updated. So they are in sync.

We can build a trigger that once Account its updated, all related contacts are updated too. This mean an Account update will fire a Case update.

B) Then, we have a request from the marketing department. “I am working on a campaign for some contacts,  when I select the checkbox that indicate the contact was chosen for the campaign, I would like to change the status of all Open Opportunities in the Account to Close Won ”

In this case we could create another trigger, so Trigger will run over Case. When the checkbox it’s marked in the case we would update all opportunities in the same account. This way a Case update will fire an Opportunity update.

C) Finally, we have another request from the Sales Team. “Once I close an Opportunity, I would like to copy several customs fields in the account and set the account status to CLOSED”

     Once again a trigger could be created to run over opportunity to meet this requirement. So when Opportunity it’s updated will fire an Account Update.

If join all the logic mentioned before, we could end with a logic like this:

Where changing a Case checkbox could make triggers to run over and over again. This it’s a simple scenario where it might be obvious how loop it’s generated, but if we are working on a big organization with hundreds of complex triggers, this kind of problem will be hard to track and detect.

For that reason it’s good to implement a “Supervisor Logic”. This can be as simple as a static class that validate if a trigger was already executed.

In this page:

https://github.com/kevinohara80/sfdc-trigger-framework/blob/master/src/classes/TriggerHandler.cls

There is a good example implemented using a static Map:

static
Map<String, LoopCount> loopCountMap;

And a method call "setMaxLoopCount" which increase the counter per trigger properly, so each Trigger is execute the times they are expected to be executed (in some occasions its ok to have an small loop, where a trigger perform several tasks).

Then in the trigger, we only execute the trigger if the trigger has not reached the max.

Boolean
exceeded = TriggerHandler.loopCountMap.get(handlerName).increment(); 

3. Features are not grouped efficiently. 

This last common reason to get the “Too many SOQL queries: 101”, its due to the lack of features groping. Its goes together.

For instance if we have 2 triggers, once that check Contact information to update the related Account and other that check Contact information to update a custom object. We could Join the logic and go the Database only once (Single SELECT).

Similarly we could join several Triggers that update Contacts based on information from other objects and make a single Contact update (Single UPDATE).

This way we would be saving several SOQLs and make the code to run faster. But since to know where or which triggers should be merged can be challenging, I would recommend to use a framework.

So far I have seen several approaches. But at the end, since every organization its unique, you will can to come up with your own custom framework.

From this link:

https://developer.salesforce.com/page/Trigger_Frameworks_and_Apex_Trigger_Best_Practices

You can take some ideas and good practices like:

·         One Trigger Per Object

·         Logic-less Triggers

·         Context-Specific Handler Methods

For complex implementations I will suggest to read this book:

Advanced Apex Programming

Dan Appleman

http://advancedapex.com/

At the end you will have your pull of classes and methods that gradually will be joining to have a scalable solution.