by Dan Beer 
As a Salesforce developer seeing the message “System.LimitException: Too many SOQL queries: 101” can be very frustrating.
In a large codebase it can be time consuming to find and fix these kind of errors. Even when your code is bulkified – why do errors remain?
They may only occur intermittently under certain circumstances, but none the less they still happen.
Quick Fix!
First off, there is no ‘one size fits all’ fix here (sorry to disappoint!). Secondly this is quite a large topic area to cover, so we will be focusing on one trigger scenario.
If you are still reading this then I’m sure you don’t need an explanation of what a trigger is in the Apex world – so why concentrate on just one scenario? Mainly because the context of the trigger governs the type of problem you are trying to solve, so the type of solution will be different.
Let’s run through a simple requirement: When an opportunity is created the customer would like the currency to be set to the corporate currency. This is for all opportunities.
I’m here for the duration
Nothing wrong with the above right? The corporate currency is being queried once outside of the loop, so what is the problem and how could we improve this?
There are two potential problems here:
- Bulk inserts – triggers execute on batches of 200 records at a time. So if 400 records cause a trigger to fire, the trigger fires twice.
- Record updates – including workflow field updates, these can cause an update trigger to rerun.
As we don’t call this class on an update, we can ignore the second point – but bulk inserts of more than 200 records will cause multiple queries just to get the same record.
How can we get around this? We need to query that Currency Type record, but do we need to query it for each trigger call?
Luckily this is easy to resolve, move the currency type to a static variable rather than an instance variable. The static variable will be available for the scope of the apex transaction and will only execute once.
Note: Defining a static variable directly in a trigger will work differently across trigger contexts.
Change is inevitable!
A new requirement has been raised by the customer: The corporate currency should only be set if the Lead Source is ‘Web’.
This is an example of a ‘quick’ fix where we have met the requirement. But we have created some technical debt, why? Because if we insert an Opportunity without a Lead Source of ‘Web’ the SOQL query is executed without using it.
Why should we care? It’s one query, and it might be used after all…
Lazy is good
Let’s fix it now! Before it goes to production, before we create technical debt. Let’s save our future self the headache of trying to resolve a limit exception error.
In our previous solution we always performed the query, this is called eager loading but for our solution we will use lazy loading.
Lazy loading will only perform the SOQL query if referenced, and it hasn’t already been loaded.
Eager is bad?
Perhaps we should avoid using eager loading, and why not switch to lazy loading even if we are always going to use the record?
This comes down to cleanliness of your code and showing your intent. I would look at the lazy loading getter above and assume there is a reason why the developer has done it this way. Lazy loading will also add more lines of code and noise around the loading of the record than eager loading.
Conclusion
I mentioned at the start that we were only covering one scenario, the query we executed didn’t change throughout our examples – but our approach to how it was executed did.
The query also didn’t change throughout the transaction, in other words we weren’t querying different records depending on the records inserted. This is why using statics variables and lazy loading is a great pattern in this situation.
There is no ‘one size fits all’ solution, but hopefully this gives you another tool for your toolbox when writing this kind of trigger.
If you have any questions, remember to get in touch.