1. About the ‘System.LimitException: Too many SOQL queries: 101’ error
System.LimitException: Too many SOQL queries: 101 error appears when you exceed the Salesforce Execution Governor limit of up to a total 100 SOQL queries in a single call or context.
Example: Below code will throw this error as it is using SOQL query in a for loop.
for (integer i = 0; i < 200; i++) {
Account acc = [select id, name from Account limit 1];
}
Notes:
- All the SOQL queries in triggers fired from one call or context will be counted against the limit of 100.
- Salesforce cannot disable or raise the Governors Limit.
2. Resolution
2.1. Avoid SOQL queries inside FOR loops
Instead of using SOQL in a for loop use SOQL For Loops. The above code can be easily modified to use SOQL For Loop.
for (Account acc : [select id, name from Account where type =: customerType]) {
}SOQL For Loops differ from standard SOQL statements because of the method they use to retrieve sObjects. While the standard queries can retrieve either the count of a query or a number of object records, SOQL For Loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of the SOAP API.
You should always use a SOQL For Loop to process query results that return many records, to avoid the limit on heap size.
2.2. Bulkify Apex Triggers
Make sure that your Apex Triggers support bulk operations. The below trigger will throw Too many SOQL queries error if more than 100 records are inserted/updated.
trigger AccountTrigger on Account (before insert, before update) {
for(Account acc : Trigger.new){
Contact con = [SELECT Id FROM Contact WHERE AccountId = acc.Id];
}
}The above trigger can be easily modified to support bulk operations as shown below.
Trigger AccountTrigger on Account (before insert, before update) {
Set<ID> ids = Trigger.newMap.keySet();
List<Contact> conList = [SELECT Id FROM Contact WHERE AccountId in :ids];
}This pattern respects the bulk nature of the trigger by passing the Trigger.new collection to a set, then using the set in a single SOQL query. This pattern captures all incoming records within the request while limiting the number of queries.
2.3. Best Practices for Designing Bulk Programs
The following are the best practices for this design pattern:
- Minimize the number of data manipulation language (DML) operations by adding records to collections and performing DML operations against these collections.
- Minimize the number of SOQL statements by preprocessing records and generating sets, which can be placed in single SOQL statement used with the IN clause.
