One of the cardinal rules for programming with Apex is never make a SOQL query in a loop.  Even when you’re 110% sure that the loop will make no more than 2 passes, you migrate your code from the sandbox to production and… BOOM!  SOQL query limit exceeded!  This is particularly bad in code that is not customer facing because it may be some time before the failure is apparent.

The answer is to always bulkify your queries.  As an example, you want to get all the Accounts assigned to the current User, then you want all the Contacts for each Account.  First, the non-bulkified example:

// We want a Map of Account IDs to all associated Contact IDs
Map <ID, List <ID>> accountContactMap = new Map <ID, List <ID>>();

// Select all Accounts owned by the current User
List <Account> accountList =
    [Select ID, Name From Account Where OwnerId =:UserInfo.getUserId()];

// For each Account get the associated Contacts
for (Account nextAccount : accountList) {
    // Bad!  Very bad!  No SOQL in a loop!  What was I thinking!?!??
    // Get the Contacts associated with this Account
    List <Contact> nextContacts =
        [Select ID, Name From Contact Where AccountId =:nextAccount.ID];
    System.debug('Account '+nextAccount.Name+' has '+nextContacts.size()+' Contacts');
   // Create a List of all the associated Contacts
   List <ID> contactIDs = new List <ID> ();
   for (Contact nextContact : nextContacts) {
        contactIDs.add(nextContact.ID);
    }
    // Add them to the Map
    accountContactMap.put(nextAccount.ID, contactIDs);
}

Now the bulkified version:

 1  // We want a Map of Account IDs to all associated Contact IDs
 2  Map <ID, List <ID>> accountContactMap = new Map <ID, List <ID>>();
 3  
 4  // Select all Accounts owned by the current User
 5  List <Account> accountList =
 6      [Select ID, Name From Account Where OwnerId =:UserInfo.getUserId()];        
 7  
 8  // Make a List of Account IDs
 9  List <ID> accountIDList = new List <ID> ();
10  for (Account nextAccount : accountList) {
11      accountIDList.add(nextAccount.ID);
12  }        
13  
14  // Now get the Contacts associated with the Accounts
15  List <Contact> allContactList =
16      [Select ID, Name        
17          From Contact        
18          Where AccountId in :accountIDList        
19          order by AccountId];        
20  if (allContactList.size() == 0) {        
20      return;        
21  }
22  
23  List <Contact> contactList = new List <Contact> ();
24  ID previousAcctID = contactList[0].AccountID;
25  
26  for (Contact nextContact : contactList) {
27      if (nextContact.AccountID != previousAcctID) {        
28          accountContactMap.put(previousAcctID, contactList);
29          contactList = new List <Contact> ();
30          previousAcctID = nextContact.AccountID;        
31      }
32      contactList.add(nextContact.ID);        
33  }
34  accountContactMap.put(previousAcctID, contactList);

The new code is longer and not as readable, but we’ve gotten rid of the dangerous SOQL in the for loop.

Notice the order by AccountId on line 19.  This is critical to insure that we make a clean transition from Contacts of one Account to another.  If you are using this pattern for more dimensions than just ID, you may have to order by multiple fields.  For example, let’s say you have a custom field Last_Ping_Date__c on Contact.  You need the Map of Account IDs to Contact IDs and you will be charting them by date.  This is simple to do in the query as shown here:

    // Now get the Contacts associated with the Accounts
    // Order by date so the chart is properly ordered
    List <Contact> allContactList =
        [Select ID, Name        
         From Contact        
         Where AccountId in :accountIDList        
         order by AccountId, Last_Ping_Date__c]; 
      

Don’t forget to test for nulls and empty collections.  The return statement on line 20 is required because we are referencing field 0 in the List on line 24.

Finally, notice the second accountContactMap.put() on line 34.  We can’t forget to add the final Account ID and its Contacts to the Map.

One consequence of this pattern is a generous use of script statements.  Of course, that may run into a different governor limit of a maximum 200,000 script statements.  Apex programming with large data sets can be a juggling act – staying under some limits may bump into others.  However, with only 100 SOQL statements to work with, bulkifying your queries should always be your first step.

Photo Credit: mccoryjames on flikr