+ Start a Discussion

Too Many SOQL Queries: 101

Hi all,


I'm receiving the "Too Many SOQL Queries: 101" error.  Last time I saw this, I had my SOQL statement w/in a FOR, but I'm not exactly sure what is going on this time.  Any help/schooling on the topic is greatly appreciated. 



public with sharing class resetQuarterlyDistCount {
	private List<Opportunity> PlansToReset;
	public list<Opportunity> getPlansToReset(){
	    PlansToReset = [Select id, Quarterly_Distribution_Count__c 
FROM Opportunity
WHERE (StageName = 'Live'
OR StageName = 'Live (Plan Termination)'
OR StageName = 'Live (Service Termination)'
OR StageName = 'Terminated (Service)'
OR StageName = 'Terminated (Plan)'
OR StageName = 'Proposal/Price Quote')]; system.debug(PlansToReset); return PlansToReset; }
 public void resetCount(){ if(PlansToReset != null) { for( Opportunity currOpty: PlansToReset ){ currOpty.Quarterly_Distribution_Count__c = 0; } update PlansToReset; } } public list<Opportunity> getPlansToResetforTest(){ PlansToReset = [Select id, Cases_this_Quarter__c from Opportunity where id='0066000000D5kFB']; return PlansToReset; } }




Making SOQL calls from get methods is not a good idea in general.  If you have bound these methods to Visualforce page, it's possible that they are getting invoked multiple times.  Consider storing method result in an instance variable and check to see if the variable is null before you make SOQL calls.  Hope this helps!


Its hard to debug the issue with just that snippet of code. From the looks of it, there is nothing wrong with the code that you posted (i.e. it follows general governor limits best practices). You'll need to explain the entire transaction - i.e.. where is this class called from, are the 'getPlansToReset' or  'getPlansToReset' methods invoked from inside a for loop etc. 


Remember that the governor limit of 100 SOQL queries applies across the entire transaction. In other words, if you're doing some SOQL queries in an 'upstream' class (i.e.. a class that invokes the resetQuarterlyDistCount) those will count against the 100 limit. The best way to debug this is to look at the debug logs closely and trace where all the SOQL queries are happening (some of which may be happening before of this class is even invoked). 


Hope this helps.


p.s. Though this has nothing to do with the error that you're getting a more efficient SOQL query would be :


PlansToReset = [Select id, Quarterly_Distribution_Count__c 
FROM Opportunity
WHERE StageName IN ('Live',
'Live (Plan Termination)',
'Live (Service Termination)',
'Terminated (Service)',
'Terminated (Plan)',
'Proposal/Price Quote')];

I just saw the previous reply by '



You could leave it in the method, but I would recommend that you write it as a caching function:



public with sharing class resetQuarterlyDistCount {
  private List<Opportunity> PlansToReset;
  public List<Opportunity> getPlansToReset(Boolean refreshCache) {
    if(PlansToReset==null || refreshCache) {
      PlansToReset = [SELECT Id,Quarterly_Distribution_Count__c
         FROM Opportunity
         WHERE StageName IN ('Live (Plan Termination)','Live (Service Termination)','Terminated (Service)','Terminated (Plan)','Proposal/Price Quote')];
    return PlansToReset;
  public List<Opportunity> getPlansToReset() {
    return getPlansToReset(false);
  // Test here

Since the results are cached, this will reduce the total number of SOQL queries to one for the entire set of calls, no matter how many times it is called. An optional parameter allows you to recache the list when necessary (say, after a DML operation that could potentially affect the list).