This topic is waaaay too big to cover in one post, so for now I’m going to concentrate on avoiding this particular exception: ‘Too many SOQL queries’. I will also touch on the ‘Too many DML rows’ exception, and expand on it’s solutions in another post. Bulkifying your triggers is another topic I will cover at some later date, but the methodologies mentioned here should go some way towards helping you avoid some of the Governor Limits there too.
Just so you know, Salesforce imposes the following restrictions on the number of SOQL queries you can issue in a single context:
20 Queries for triggers
100 Queries for test methods
100 Queries for Anonymous Blocks, Visualforce Controllers, or WSDL Methods [Aside: in the documention it looks as though you can perform 100 to the power of 8 queries, but the ‘8’ is just a link to a footnote. Or am I the only one that thought that? :-$]
Note that the entry point of your code determines the Governor Limit applied e.g. if a trigger is fired and that trigger calls methods of an Apex class the overall query limit will be 20. More details here.
Obviously this means that developers need to be quite frugal(nice word huh?) when working with queries, and as long as there’s no word to rhyme with orange we should never issue an SOQL query within a for-loop.
Let’s have a look at the type of code that could cause this issue
public void rhymeWithOrange(List<String> theNames){
for(String theName: theNames){
myObject__c obj = [SELECT processed__c FROM myObject__c WHERE name=:theName]; // Mistake #1 – may result in ‘Too many SOQL queries’
obj.processed__c = true;
update obj; // Mistake #2 – may result in ‘Too many DML rows’
}
}
Let’s address Mistake #1. The SELECT statement will be run for each iteration of the loop i.e. a list of 200 names means 200 queries, which means 200 round trips, which means this isn’t going to work. The solution is to fetch all records for the object before the loop and then loop through the list of objects returned, thereby bypassing the need to query each time.
Mistake #2 has an elegant solution too, and it’s solution will go some way towards helping avoid the ‘Too many DML rows’ exception. Salesforce has made it exceedingly simple to perform mass DML simply by issuing a single DML command for a list of objects. The solution in the above case is to use an intermediate list to store all the updated object records and issue the update DML command after the loop.
The two solutions combined would be
public void rhymeWithOrange(List<String> theNames){
List<myObject__c> objToUpdate = new List<myObject__c>(); // intermediate list
List<myObject__c> objects = [SELECT processed__c FROM myObject__c];
for(String myName: theNames){
for(myObject__c obj: objects){
if(theName==obj.name){
obj.processed__c = true;
objToUpdate.add(obj); // add processed objects to intermediate list
}
}
}
update objToUpdate; // update all objects at once
}
[Aside: There is a more concise and efficient solution, but the above code should serve as a fairly generic template to apply within your code. Feel free to improve on the above solution.. perhaps as an exercise. What’s that.. you want me to point you in the right direction? Alright then, try this on for size, search the Apex Language Reference for ‘Working with Very Large SOQL Queries’.]
You may not be aware of the governor limits when you start your Force.com development but it’s a nice surprise when you discover them. Not really. I’m lying, it’s a horrible surprise.. like discovering the easter bunny isn’t real. Oh the torment!
That said, even after I knew about these best practices I was slow to implement them.. until I realised the amount of time it takes to re-factor complex, inefficient code is MUCH larger than doing things correctly the first time round. And I don’t use caps lightly.
Long story short, don’t be a lazy developer.*finger-wag*
Excellent timing – we’ve just published an article on this same topic 🙂
http://wiki.developerforce.com/index.php/Governors_in_Apex_Code
Superb post…..waiting for more…
keep doing your exerllent work.
Another way around them, at least on VF pages, is just to use jQuery to issue multiple calls: You have a “meta-page” that calls “page=lets” with differing query string params.
Each ajax call is treated as a separate request. Nice way around the lame 1000 item limit on Lists and such too…
😉
Shameless plug – Check us out:
http://www.appiphony.com
Download our free 100% native blogging engine.
I just had to builkify some more advanced triggers than example code usually goes into, and figured I should give some examples of what I ended up doing:
the code previously looked like this:
public static void setContactData(ourObject__c[] objects){
for (outObject__c currentobject:objectss){
Contact currentContact;
currentContact = [ Select Phone, MobilePhone, Email,Department__c, Fax From Contact where Id = :currentIncident.Contact_Name__c];
currentIncident.Contact_Mobile__c = currentContact.MobilePhone;
currentIncident.Contact_Phone__c = currentContact.Phone;
currentIncident.Contact_Fax__c = currentContact.Fax;
currentIncident.Contact_Email__c = currentContact.Email;
currentIncident.Department__c = currentContact.Department__c;
}
}
Obviously a tirgger calling that method would be killed by a govenor at 20 records, but how to update fields that are linked to another sObject, in this case Contact, took me longer than I’d have hoped.
In the end it ended up looking like this:
public static void setContactData(ourObject__c[] ourObjects){
//Make a list out of all the contacts refernced in ourObjects[]
List referencedContacts = new List();
for (ourObject__c currentourObject:ourObjects) {
referencedContacts.add(currentourObject.Contact_Name__c);
}
Map Contacts = new Map([Select Id, Phone, MobilePhone, Email,Department__c, Fax From Contact where Id IN :referencedContacts]);
for (ourObject__c currentourObject:ourObjects){
Contact currentContact = Contacts.get(currentourObject.Contact_Name__c);
currentourObject.Contact_Mobile__c = currentContact.MobilePhone;
currentourObject.Contact_Phone__c = currentContact.Phone;
currentourObject.Contact_Fax__c = currentContact.Fax;
currentourObject.Contact_Email__c = currentContact.Email;
currentourObject.Department__c = currentContact.Department__c;
}
}
rather than 20 records, I can now query 200. The same trick of making an array of IDs and then pulling all the results the loop will need into a map worked wonderfully for our update triggers.