+ Start a Discussion
mallikammallikam 

execution of BeforeInsert caused by: System.Exception: Too many SOQL queries: 21

Hi,

 

I have a trigger with the following code, everything seems to be working fine until I got the above exception through email this morning. The code looks simple and correct to me an so I am having hard time figuring out why the query could have run into governor limits exception..

 

trigger Executive on Case (before insert, before update) { Map<Id, Case[]> contactCaseMap = new Map<Id, Case[]>(); For (Case ncase:Trigger.new) { Id cid = ncase.contactId; If (cid != NULL) { if(contactCaseMap.get(cid) == null) contactCaseMap.put(cid, new Case[]{}); contactCaseMap.get(cid).add(ncase); } } For(Contact c:[select Executive__c from Contact where Id IN :contactCaseMap.keySet()]) { // code is // running into exception at this // query

 

Thanks!

Anand@SAASAnand@SAAS
You should not be executing a SOQL in a "for loop". Refer to this link, especially Best Practice#2.
Cool_DevloperCool_Devloper

MM,

 

Is there a query written inside the SOQL FOR loop?

 

Could not make out clearly from your code, so wanted to confirm!

 

Cool_D

mallikammallikam

Thanks Anand and CD for the reply! And CD, here is the complete code:

 

For(Contact c:[select Executive__c from Contact where Id IN :contactCaseMap.keySet()]) {// exception here for(Case ncase:contactCaseMap.get(c.id)) { ncase.Executive__c = c.Executive__c; } } }

I dont have a query inside For loop but in FOR statement itself and thats where I am getting exception. I think Anand is right!

Message Edited by mallikam on 11-16-2009 07:16 AM
Cool_DevloperCool_Devloper

Hi,

 

I think you understood it wrongly. 

If i am reading your code correctly, you are using a SOQL FOR loop which is different from query inside a FOR Loop! 

In this case, the exception is not because of this loop but it is occuring as you are executing this inside the outer Trigger.New loop.

Your query is absolutely fine and is the right way of writing it. Once you pur it outside the outer loop, the code would work like charm!!

 

Cool_D

PS: The same concept is explained in the link posted above.

mallikammallikam

But it shows me where the exception was triggered and it was triggered at this particular line..

 

For(Contact c:[select Executive__c from Contact where Id IN :contactCaseMap.keySet()])

 

And moreover the outer for loop ends right above this SOQL for statement, meaning the query is already outside of the Trigger.new for loop?! Here is the complete code from the beginning:

 

trigger Executive on Case (before insert, before update) { Map<Id, Case[]> contactCaseMap = new Map<Id, Case[]>(); For (Case ncase:Trigger.new) { Id cid = ncase.contactId; If (cid != NULL) { if(contactCaseMap.get(cid) == null) contactCaseMap.put(cid, new Case[]{}); contactCaseMap.get(cid).add(ncase); } } For(Contact c: [select Executive__c from Contact where Id IN :contactCaseMap.keySet()]) { for(Case ncase:contactCaseMap.get(c.id)) { ncase.Executive__c = c.Executive__c; } } }

 

 

 

Cool_DevloperCool_Devloper

Well, code looks fine to me! 

Did you try using an inline SOQL instead of the SOQL FOR loop? 

If not, then try that out. But this query seems fine as it is already outside the loop.

 

 Cool_D

mallikammallikam

Thanks CD for the confirmation. I have seperated the query from the FOR statement for now, lets see. I had hard time debugging this one because even I did not see any issues with code either..!!

mallikammallikam

CD,

 

I ran into the same problem again. I changed the code to below:

 

trigger Executive on Case (before insert, before update) { Map<Id, Case[]> contactCaseMap = new Map<Id, Case[]>(); For (Case ncase:Trigger.new) { Id cid = ncase.contactId; If (cid != NULL) { if(contactCaseMap.get(cid) == null) contactCaseMap.put(cid, new Case[]{}); contactCaseMap.get(cid).add(ncase); } } List<Contact> ExecutiveOfContact = [select Executive__c from Contact where Id IN :contactCaseMap.keySet()]; For(Contact c: ExecutiveOfContact) { for(Case ncase:contactCaseMap.get(c.id)) { ncase.Executive__c = c.Executive__c; } } }

 

 

and I got the same error again today:

Executive: execution of BeforeInsert

 

caused by: System.Exception: Too many SOQL queries: 21

 

Trigger.Executive: line 12, column 36

 

 

I really dont understand what exactly is causing the problem!! I dont understand if it is the FOR statement or if it is the SOQL statement itself that is causing this..any help?

 

thanks!

 

 

Message Edited by mallikam on 11-18-2009 09:48 AM
Cool_DevloperCool_Devloper

Hi There,

 

I would re-write the code like this-

 

trigger Executive on Case (before insert, before update) { Set<String> contactSet = new Set<String>(); For (Case ncase:Trigger.new) { If (ncase.contactId != NULL) { contactSet.add(ncase.contactId); } } Map<ID, Contact> ExecutiveOfContact = new Map<ID, Contact>([select id, Executive__c from contact where Id IN :contactSet]); for(Case ncase:Trigger.new) { ncase.Executive__c = ExecutiveOfContact.get(ncase.contactId).Executive__c; } }

 

Can you please try running this?

 

Cool_D

mallikammallikam

Thanks a lot, CD!! Now this works like a charm! :)

 

This looks certainly less problematic and straight forward than the original code but I dont know if I can find out if this is a correct choice over the previous one immediately..the problem with my original approach was that I could not reproduce the exception. It was running into the governor limit exception only under certain circumstances on production, I could not figure out what it was. But I think I will deploy this on production and see if this code runs me into an exception in future...thats the best I can do for now?! 

Cool_DevloperCool_Devloper

Well, the query was always looking fine.

 

Only thing was ... there were some extra loops which could have been avoided.

 

hope this does'nt give exceptions for you!!

 

Cool_D

mallikammallikam
Still getting the same exceptions...so I replaced it with original code thinking that the frequency of exceptions with original code is low..!!! I am now thinking if I should totally get rid of for loops!!
Cool_DevloperCool_Devloper

MM,

Can you please check if there is any workflow/field update, which might run the trigger again? Try seeing the debug log.

I am sure, code is fine, no issues. Certainly, there would be some other aspect of it which is being missed out!

Also, see if you can put any checks, like checking if contact value has changed as only then this logic needs to run, right?

Cool_D