function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
guest1231231guest1231231 

System.Exception: Too many SOQL queries: 21

Receiving this error:

 

Apex script unhandled trigger exception by user/organization: 00500000006r7X9/00D00000000havX

 

CopyContactDateToOppy: execution of AfterInsert

 

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

 

Trigger.CopyContactDateToOppy.CopyContactDate: line 75, column 11

 

Here is the code...

 

 

Map<Id, Opportunity []> oppsMap = new Map<Id, Opportunity[]>();

//create list of related accounts and map them to the opportunities
List<Account> accts = new List<Account>{};
//this query below is line 75
accts = [select Id, (select Id from Opportunities where CloseDate > 2009-03-01)
from Account
where Id in:setAccountIdsSet and Id in (select AccountId from Opportunity where CloseDate > 2009-03-01)];

for (Account eachAcct: accts) {
oppsMap.put(eachAcct.Id, eachAcct.Opportunities);
}

 

What am I doing wrong here?

 

 


Anand@SAASAnand@SAAS

Not exactly sure what your business logic is but instead of trying to query for Account, you should be querying for Opportunity and programatically group them in your for loop. E.g. something like this:

 

 

Map<Id, Opportunity []> oppsMap = new Map<Id, Opportunity[]>();
		
//create list of related accounts and map them to the opportunities
List<Opportunity> opptys;
//this query below is line 75
opptys = [select Id,AccountId from Opportunities where CloseDate > 2009-03-01];
for (Opportunity oppty : opptys) {
  opptys = opptyMap.get(oppty.AccountId);
  if(opptys==null){
    opptys = new List<Opportunity>{};
    opptyMap.put(oppty.AccountId,opptys);
  }
  opptys.add(oppty);	
}

 

 

guest1231231guest1231231

I updated the SOQL to query on the opportunity object instead of the account but I am still receiving the error.

 

 

Opportunity [] oppsList =	[select Id, AccountId, OwnerId from Opportunity where CloseDate > 2009-03-01 and AccountId in:setAccountIdsSet];

 

 

AlexPHPAlexPHP

This exception is not necessarily caused by one particular query (though it may be if you put a query inside of a loop, for example).

 

This exception comes from the data flow for the data transaction you are performing doing more than the governor limit of 20 SOQL queries.  More information at: http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_gov_limits.htm

 

The query that it may reference in the exception is the 21st one in your transaction.

 

You will need to look into the debug log to fully track what other Triggers/Classes that were executed during your transaction to see where you are doing SOQL queries.  You should avoid putting any queries inside loops, for example, as that will cause an increase in the number of queries.  After evaluating your code, you will need to restructure it to reduce any duplicate data fetches/DML executions.

guest1231231guest1231231

None of my queries in this trigger are inside a loop, what are some other common mistakes that I'm probably be making?

 

This is the line of code that is now getting the Too Many SOQL queries error.  It should also be noted that this does not always happen, only when the trigger results in firing an expected validation rule error. More specifically, it is the line update OppysToUpdate.values(); that it does not seem to like.

 

 

//create map of related opportunities to update
Map<Id,Opportunity> OppysToUpdate = new Map<Id,Opportunity>();

if(OppysToUpdate.size()>0) {
update OppysToUpdate.values();
}

-Thanks

 

 

 

Jeremy.NottinghJeremy.Nottingh

What is this trigger firing on? If it's on Opportunity, then you're calling the Trigger from within itself, which would be an infinite loop.

 

Probably you've got something along these lines happening with other Triggers or something. Look at the system debug log for clues on what might be running at the same time as this Trigger. One bad Trigger in your org can spoil it for everyone else who's conserving their SOQL queries.

 

Jeremy

AlexPHPAlexPHP

Yes, as Jeremy noted, one bad trigger can mess things up.

 

All your queries might be sound and following good practices.  But again, as I stated before... after you have evaluated the data transaction flow (every trigger/class that executes in the transaction), you will likely have to restructure your code to prevent duplicate data fetches/DMLs.

 

Possible approaches you may consider:

1) Consolidating triggers... if you have multiple triggers for the same object, it might be possible to combine their functionality to preserve queries

 

2) Structure your code so that data already fetched by default by the trigger is not re-fetched.

 

3) Structure your code so that data you have fetched manually is not re-fetched by another trigger.

 

4) Using @future annotation to perform logic outside of the data transaction.

 

 

For example... the following two queries correspond to objects that are related (parent and child)...

 

 

List<Id> activeParentIds = [Select Id From Parent__c Where Active__c = true];
List<Id> activeChildIds = [Select Id From Child__c Where Parent__c IN :activeParentIds];

Here it is trying to get active Child Ids, based on if its parent is active.   This can be combined into one query since there is a master detail relationship between them...

 

List<Id> activeChildIds = [Select Id, Parent__r.Id From Child__c Where Parent__r.Active__c = true];

 By doing this, you save a query :)

 

 

guest1231231guest1231231

The trigger is firing on the Task object.  The basic logic is for all tasks related to the opportunity, and assigned to the opportunity owner, get the task with the earliest date, then copy that date to a custom opportunity field.

 

If another trigger is the problem, wouldn't the error mention the other trigger?

AlexPHPAlexPHP

No, this is a governor limit error.

 

It simply counts how many queries you are doing in your transaction, and whoever breaks the limit is the one that is shown on the error message.

 

As I've said, can very well be just the way you structure your code to handle your business logic.