Too many SOQL queries: 21

Thursday, April 22, 2010 by Aslam - The Alexendra
Hi,
Here i am going to tell you about some good practice to avoid "Too many SOQL queries: 21" error on Triggers. I got this approach from one of my good colleague "Prakash Gyamlani" (Thanks for him).

Here is some background what problem i faced:-

In one of my organization i had 7 triggers on Case. Some triggers are "before update", some triggers are on "after update". In some triggers i was also updating Case object itself based on some criteria. So it generally again calls some Triggers ( I have taken care of Recursion, that's not the problem).
After some days when i tried to save one case record i started getting "Too many SOQL queries: 21" error. I search debug logs and found that my queries are going beyond the 20. After some research i found the area which was causing the root problem.

Root Problem:-

There was one "Before Update" trigger on Case which was querying Record Types on top of the trigger like below:-

trigger SendEmailsOnCaseComment on Case (before update) {

Map<id,RecordType>mapRecordTypes = new Map<id,RecordType>([SELECT Id, Name from RecordType WHERE SObjectType = 'Case']);

for(Case cs: Trigger.NEW){
//logic
}
//extra code
}

The problem in the above code is that the query on top will always be executed whenever our control comes in this trigger. Suppose because of 7 triggers, on Case update (and inner updates), this trigger is called 6 times then we have 5 time SOQL queries extra. We only interested to execute this query one time only within the execution of a Trigger Thread.

How to solve this:-

So we should code our trigger in this way that if there are any queries which are general, not dependent on any conditions, then put those queries in a place where they can be executed only once. And Whenever we need the results we get our results always in single execution of Trigger Thread.


Code:-
The solution for above problem is to code like this:-

Make one utility class for getting record types and initialize that (Fire SOQL) in a "Static" block. In this way we will always have that soql fired only once.

public class CaseManagementUtility{
public static Map<id,RecordType> mapRecordTypes = null;
static {
mapRecordTypes = new Map<id,RecordType>([SELECT Id, Name from RecordType WHERE SObjectType = 'Case']);
}

public static Map getRecordTypeMap(){
return mapRecordTypes;
}
}


Now, in our trigger use this code like this:-
trigger SendEmailsOnCaseComment on Case (before update) {

Map<id,RecordType> mapRecordTypes = CaseManagementUtility.getRecordTypeMap();


for(Case cs: Trigger.NEW){
//logic
}

//extra code
}

Now, suppose above trigger is called 10 times (in a single execution), but SOQL query will be fired only once.


Quite simple. Isn't it.

Thanks
Aslam Bari
Posted in | 5 Comments »

5 comments:

Jeff Douglas said...

Aslam, I think the code above has a typo as it throws an error for me. It looks like the blog stripped out the left and right brackets. I think

Map mapRecordTypes = = new Map([SELECT Id, Name from RecordType WHERE SObjectType = 'Case']);

Should be replaced with

Map<ID,RecordType> mapRecordTypes = new Map<ID,RecordType>([SELECT Id, Name from RecordType WHERE SObjectType = 'Case']);

Tom Patros said...

You can also save some SOQL queries by using Describe information.

Schema.SObjectType.Case.getRecordTypeInfos() will return all the RecordTypes for the case as RecordTypeInfo objects.

There are governor limits on RecordTypeInfo-related calls, but you can at least divert some SOQL calls away to free them up.

Aslam - The Alexendra said...

@Jeff:- Thanks for pointing out the typo mistake. I fixed it.

@Tom:- Its good to know more about RecordTypeInfos(). Thanks for sharing.

Unknown said...

Aslam,

For this specific scenario, wouldn't it be nicer to rather just leverage the API call to get recordtypeInfos and then use the getRecordTypeId() method to get the specific one....

Abhinav Gupta said...

Nice post Aslam !

Its good to see utilization of static context in this way, as you said execution thread is same for all triggers. Using Schema.SObjectType.Case.getRecordTypeInfos() is equally good if we cache the results similarly in some static context to save further describe calls.

Post a Comment