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

Twitter Search Widget Integrate With Salesforce

Monday, April 19, 2010 by Aslam - The Alexendra
Hi,
I recently came across with 'Twitter Search Widget', which is very useful for getting recent tweets for in which topic you are interested and you can embed this widget anywhere you like for example your salesforce org, public site, blogger etc.

I created one search widget for Salesforce Business Tweets and Embedded this to Sidebar of the salesforce org. See below:-



For Getting the Search Widget For your Site try this:-

1) Go To this link Twitter Widget
2) Set your search preferences
3) Get the Code and Paste into your side or blog

Thanks
Aslam Bari