function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
GMASJGMASJ 

Too many SOQL queries: 101 in a method call

I have a method written which is been called inside a visualforce page table if there are less rows there is no issue. when table has more rows it is throwing error as mentioned below 

Too many SOQL queries: 101​
  
/* calculate disti discount */  
  public static Decimal reCalRslrDisc(Decimal listprice,String productcat, Decimal reslrdistdiscount){
        Boolean isService = false;
        
        system.debug('listprice = ' + listprice);
        system.debug('productcat = ' + productcat);
        system.debug('reslrdistdiscount = ' + reslrdistdiscount);
        
        if ( reslrdistdiscount == null || reslrdistdiscount < 1 ){
            reslrdistdiscount = 0;
         } 
        
        if ( productcat=='C' || productcat=='E'|| productcat=='D'|| productcat=='H'|| productcat=='I'|| productcat=='J' ){
            isService = true;
          }
        
        if ( isService == true ) { 
            NSP_Margin_Schedule__c NMS =  [ Select Distributor_Discount__c From NSP_Margin_Schedule__c 
                                           where  Reseller_Discount__c = :reslrdistdiscount and 
                                           Service__c = :isService and 
                                           createddate < 2015-01-17T00:00:00-08:00 ];
            
            return NMS.Distributor_Discount__c;
        }
        else {
            return 0;
        }      
        
  }

Error is happening in above code while returning query return NMS.Distributor_Discount__c;

Please suggest me a way how to avoid this error can we use map to over come this if so please suggest me with an example 

Thanks
Sudhir
BALAJI CHBALAJI CH
Hi Sudhir,

The above class seems to be Alright. But the class might be called many times which has a SOQL executing many times.
Not sure how you are calling the class, but make sure you pass list of values to this class and call class limited times to avoid Governor Limits.

Best Regards,
BALAJI
GMASJGMASJ
Thanks Balaji how to avoid the calls am calling this method inside another methods and that is been used inside visualforce page is there any alternative method using MAP or ?

Thanks
Sudhir
BALAJI CHBALAJI CH
You are calling this method from another method for each values of "Decimal listprice,String productcat, Decimal reslrdistdiscount". Instead, you can keep all the values which are being processed in the Map with a unique key and by pass this Map with all values to the method once and in this method you can iterate and assign value.
I understand its little confusion to explain over here. If you can provide the method from which this method is being called and from where the parameters are being passed, we can try to help more.

Best Regards,
BALAJI
GMASJGMASJ
Balaji this is how am calling the methods 
  
/* calculate disti discount */  
  public static Decimal reCalRslrDisc(Decimal listprice,String productcat, Decimal reslrdistdiscount){
        Boolean isService = false;
        
        system.debug('listprice = ' + listprice);
        system.debug('productcat = ' + productcat);
        system.debug('reslrdistdiscount = ' + reslrdistdiscount);
        
        if ( reslrdistdiscount == null || reslrdistdiscount < 1 ){
            reslrdistdiscount = 0;
         } 
        
        if ( productcat=='C' || productcat=='E'|| productcat=='D'|| productcat=='H'|| productcat=='I'|| productcat=='J' ){
            isService = true;
          }
        
        if ( isService == true ) { 
            NSP_Margin_Schedule__c NMS =  [ Select Distributor_Discount__c From NSP_Margin_Schedule__c 
                                           where  Reseller_Discount__c = :reslrdistdiscount and 
                                           Service__c = :isService and 
                                           createddate < 2015-01-17T00:00:00-08:00 ];
            
            return NMS.Distributor_Discount__c;
        }
        else {
            return 0;
        }      
        
  }

reCalRslrDisc method is called in below method  
/* Call calcOnReDistDschange and calcOnReDistSalPrcchange methods */
  public PageReference callDistSaleMth(){
        Decimal glistprice;
        Decimal chkresller = 0;
        
        for(OptyLineWrapper linew : lineWrapr){        
            OpportunityLineItem backuplne = backUpLines.get(lineW.line.id);
 
            if ( lineW.line.PricebookEntry.name != 'COTERM' ){
                glistprice = lineW.line.listprice;
            }
            else {
                glistprice = lineW.line.CoTerm_List_Price__c;                 
            }   
            
            if ( lineW.line.Reseller_discount_rate__c  == NULL ){
                chkresller = 0;
            } 
            else{
                chkresller = Decimal.Valueof(lineW.line.Reseller_discount_rate__c);   
            } 
            
            lineW.line.Discount_rate__c = String.valueOf(reCalRslrDisc(glistprice,lineW.line.PricebookEntry.Product2.Category__c,chkresller)); 
            lineW.line.UnitPrice = reCalRslrDiscSalPrc(glistprice,lineW.line.PricebookEntry.Product2.Category__c,chkresller);
           }    
         editlinestatuschng(); 
         return null;
  }

Thanks
Sudhir
Agustina GarciaAgustina Garcia
Sudhir, I'm afraid you are getting the error because you are making your call inside of a loop. So if the loop has 3 iterations, then it works fine, however if the loop has more than 100 iterations, then, you would hit Salesforce SOQL governor limit (https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm)

You should develop thinking that your method could be called in bulk in order to avoid these issues. Let me show you a quick example:
  
public Account myInternalMethod(Id accountId)
{
	Account acc = [Select Name from Account where Id = :accountId];
	return acc;
}

public void myCallerMethod()
{
	List<Id> accountIdList = ... //you get this information somehow.

	for(Id accId : accountIdList) //If this list has more than 100 Ids, then it would fail
	{
		Account acc = myInternalMethod(accId); 
		//do something else with acc
	}
}

//To solve it
public List<Account> myInternalMethod(List<Id> accountIds)
{
	List<Account> accList = [Select Name from Account where Id In :accountIds];
	return accList;
}

public void myCallerMethod()
{
	List<Id> accountIdList = ... //you get this information somehow.

	List<Account> accList = myInternalMethod(accountIdList);
	for(Account acc : accList)
	{ 
		//do something else with acc
	}
}

Let me know if this doesn't help
Agustina
anil jadhav 8anil jadhav 8
Hi Sudhir,

The best way is get rid of this problem is 
1. Analyze all the queries used in your org.
2. Analyze which queries are used repeatedly.
3. and remove all the unneccesary queries and make use of singleton pattern.

this might help you.

Regards,
Anil 
BALAJI CHBALAJI CH
Hi Sudhir,

Agreeing with @Agustina.
Instead of passing paramaters separately to the Method and returning a value to be updated in the record from the Wrapper  "linew.line"., try passing the list of records to the method and do required validations and updations in the method and return the list of records with updated value.

Best Regards,
BALAJI