Workarounds to avoid Error: "Too many SOQL queries: 101"

Question

I am hitting salesforce limits in 3 cases:

1. When user is adding a document from a large list of docs which is greater than 100? (Eg error when they are trying to pull a document from their library that has 196 documents)
2. When processing a list of ATTACHMENTS greater than 100.
3. When an Opportunity has 100 contacts, the controller throws error.

There are two Opportunities that will show the error above:

1. Opportunity "Opp with 100 Contacts 1 Document", if we click on custom button on action.
2. Opportunity "Opp with 1 Contact 102 Documents", if we click on custom button on action.

Any help with architecture redesign/ Work around/ better handling of such errors appreciated

Answer 1

I always like to think about future when I write code. I had a similar issue when I started writing Apex a few years ago, so not to worry you are not the only one.

Please look at my advices below:

1. Read this article carefully: http://wiki.developerforce.com/page/Best_Practice%3A_Bulkify_Your_Code
2. Make sure you don't have a recursive loop calling a SOQL.
3. Do not do any DML/CRUD inside a for loop.
4. Use System.Debug() to ensure what you are fetching is what you should be getting.
5. Make use of the Debug logs/Developer console. Debug it, and you will figure it out.

I am also happy to help with code if you can post it up.

Answer 2

In addition with above given points (consolidated):

• Avoid SOQL queries that are inside FOR loops.
• Make sure you don't have a recursive loop calling a SOQL.
• Do not do any DML/CRUD inside a for loop.
• Avoid more than one DML on single object in single transaction - because each DML invokes trigger of related object can cause addition of SOQL count.
• Avoid too many field updates in process builders in different conditional branches. Each branch runs a DML and initiate trigger execution.
• If there are lot of business processes and logic in your trigger then design and isolate processes with help of future and batch if needed.
• Recursion Handling - To ensure a trigger is not running recursively.

• Minimize the No.of SOQLs by merging the queries - we will merge the SQL query of single object in a single query if possible. For Example:

  Opportunity op = [select StageName, Account.OwnerId From Opportunity where id: opId];
  List olis = [select id, productId from OpportunityLineItem where OpportunityId =: op.Id];

Change it to:

Opportunity op = [select StageName, Account.OwnerId, (select id, productId from OpportunityLineItems) From Opportunity where id: opId];