+ Start a Discussion
MoggyMoggy 

System.LimitException: Too many SOQL queries: 101

I have written a trigger to fill some lookup fields depending on a given number

it worked fine until you try to mass update.

here is the trigger

trigger getTradingAccountUPD on Case (before update) {
List<Contract> abc = new List<Contract>();
List<Account> mta = new List<Account>();

for(Case oldCase :trigger.old){
    if(oldCase.Trading_Account__c == null && oldCase.Master_trading_Account__c == null && oldCase.Customer__c == null){

for(Case nCase :trigger.new){
    if(nCase.Legacy_Account_Number__c != null){
        nCase.System_Notification__c ='';
        abc = [SELECT Id,AccountId FROM Contract WHERE Legacy_Account_Number__c =: nCase.Legacy_Account_Number__c LIMIT 1];
        if(abc.size()>0){
           
              nCase.Trading_Account__c = abc[0].Id;
              nCase.Master_Trading_Account__c = abc[0].AccountId;
              mta = [SELECT ID,ParentId FROM Account WHERE Id =: abc[0].AccountId LIMIT 1];
              if(mta[0].Id != null) {
                  nCase.Customer__c = mta[0].ParentId;    
                  }  
            }
         if(abc.size() == 0) {
             mta = [SELECT Id,ParentId FROM Account WHERE Legacy_Account_Number__c =: nCase.Legacy_Account_Number__c LIMIT 1];
             if(mta.size() >0) {
             nCase.Master_Trading_Account__c = mta[0].Id;
             if(mta[0].ParentId != null) {
                 nCase.Customer__c = mta[0].ParentId;
                 }
             }    
          }
          if((abc.size()==0) && (mta.size()==0)){
              nCase.System_Notification__c = '(NOT FOUND IN DATABASE)';
              }
        }
     }
   }
  }
}

 Okay I saw that the select query is in the loop so i moved my head around and came up with the following

 

 

trigger getTradingAccountUPD on Case (before update) {

List<Account> cus = new List<Account>();

Set<id> mta = new Set<id>();
Map<id,String> cases = new Map<id,String>();

 for(Case oldCase :trigger.old){
    if(oldCase.Trading_Account__c == null && oldCase.Master_Trading_Account__c == null && oldCase.Customer__c == null){
       for(Case ncase :trigger.New){
          cases.put(ncase.Id,ncase.Legacy_Account_Number__c);
          ncase.System_Notification__c = '';
       }
    }
 }
Set<Contract> ta = new Set<Contract>([SELECT Id,AccountId,Legacy_Account_Number__c FROM Contract WHERE Legacy_Account_Number__c IN : cases.values() Limit 1]);
If(!ta.isEmpty()){
  for(Case ncase :trigger.New){
    if(ncase.Legacy_Account_Number__c != null){
        for(Contract lan : ta){
            if(ncase.Legacy_Account_Number__c.equals(lan.Legacy_Account_Number__c)){
                ncase.Trading_Account__c = lan.id;
                mta.add(lan.AccountId);
            }
        }
    }
   }
 }
 if(!mta.isEmpty()){
     List<Account> acc = new List<Account>([SELECT Id,ParentId FROM Account WHERE Id IN : mta]);
     for(Case ncase :trigger.New){
       ncase.Master_Trading_Account__c = acc[0].Id;
       if(acc[0].ParentId != null){
           ncase.Customer__c = acc[0].ParentId;
           }
       }
  }      
 if(ta.isEmpty() && mta.isEmpty()){
     List<Account> amaster = new List<Account>([SELECT Id,ParentId,Legacy_Account_Number__c FROM Account WHERE Legacy_Account_Number__c IN : cases.values()]);
        if(!amaster.isEmpty()){
          for(Case ncase :trigger.New){
            ncase.Master_Trading_Account__c = amaster[0].Id;
            if(amaster[0].ParentId != null){
              ncase.Customer__c = amaster[0].ParentId;
            }
          }
        } 
        else if ((amaster.isEmpty()) &&(ta.isEmpty() && mta.isEmpty()) ){
          for(Case oldCase :trigger.old){
              if(oldCase.Trading_Account__c == null && oldCase.Master_Trading_Account__c == null && oldCase.Customer__c == null){
                 for(Case ncase :trigger.New){
                    ncase.System_Notification__c = 'NOT FOUND';
                 }
               }  
           } 
         }    
 }
    
           
}

 but now my test class runs into the same System.LimitException: Too many SOQL queries: 101 issue at

Set<Contract> ta = new Set<Contract>([SELECT Id,AccountId,Legacy_Account_Number__c FROM Contract WHERE Legacy_Account_Number__c IN : cases.values() Limit 1]);

 

Any Idea how can I avoid that issue, because there are regular updates with the data loader

 

some backgroud

the account number is an external id which will be pulled into the legacy_account_number_ c

 

this number is pointing to a Contract object ( unique ), depending on that contract I need to pull the

ID of the related Account Object from that Contract and if any the parent account of that account.

 

I just wonder if there is anything else I could render to avoid the SQL limits

 

Thanks in advance

 

my testclass looks like that

@isTest(SeeAllData=true) 
public class getTradingAccountTest {
static testMethod void test_getTradingAccount(){
 Test.startTest();
        Case cs = new Case();
        cs.email__c = 'test.test@gmail.com';
        cs.Preferred_Reply_Method__c = 'Letter';
        cs.Post_Code__c = '95050';
        cs.Phone__c = '408123123';
        cs.First_Line_of_Address__c = 'test address';
        cs.Tracking_Job_Number__c = '1234567';
        cs.Consignment_Number__c = '24242323';
        cs.description= 'test';
        cs.Account_Number__c = '38J010W';
        insert cs;
        
        Case ds = new Case();
        ds.email__c = 'test.test@gmail.com';
        ds.Preferred_Reply_Method__c = 'Letter';
        ds.Post_Code__c = '95050';
        ds.Phone__c = '408123123';
        ds.First_Line_of_Address__c = 'test address';
        ds.Tracking_Job_Number__c = '1234567';
        ds.Consignment_Number__c = '24242323';
        ds.description= 'test';
        ds.Account_Number__c = 'test0001';
        insert ds;
        
        Case es = new Case();
        es.email__c = 'test.test@gmail.com';
        es.Preferred_Reply_Method__c = 'Letter';
        es.Post_Code__c = '95050';
        es.Phone__c = '408123123';
        es.First_Line_of_Address__c = 'test address';
        es.Tracking_Job_Number__c = '1234567';
        es.Consignment_Number__c = '24242323';
        es.description= 'test';
        es.Account_Number__c = 'rolle roe';
        insert es;
        
        //for code coverage in the controller
        NewCaseController x = new NewCaseController();
        x.trackAccount(cs);
        x.trackAccount(ds);
        x.trackAccount(es);
        //end of code coverage in controller
        
        es.Legacy_Account_Number__c = '38J010W';
        update es;
        es.Legacy_Account_Number__c=null;
        es.Trading_Account__c=null;
        es.Master_Trading_Account__c=null;
        es.Customer__c=null;
        update es;
        es.Legacy_Account_Number__c='test0001';
        update es;
        es.Legacy_Account_Number__c=null;
        es.Trading_Account__c=null;
        es.Master_Trading_Account__c=null;
        es.Customer__c=null;
        update es;
        es.Legacy_Account_Number__c='nope';
        update es;
        
        Test.stopTest();
}
}

 

 

Dream_weaverDream_weaver

Use Test.startTest() & test.stoptest() after 1/2 insert statement in testclass...it should work...It will breakdown the context of one trigger and will allows more SOQL query..

Eli Flores, SFDC DevEli Flores, SFDC Dev

Are there other triggers involved in a case? The governor limits apply to the whole set of triggers not just 1. This looks pretty good so i'd supspect it's inefficient queries in the other triggers.

MoggyMoggy
nope
System.FinalException: Testing already started
Stack Trace (System Code)
Class.getTradingAccountTest.test_getTradingAccount: line 41, column 1

that is where i inserted test.stopTest() and line 41
test.start.test()

I think you can only have one start and stop at all
MoggyMoggy
cheers, I will check this out there are a few triggers from other people and pretty old ones
I will check them
MoggyMoggy
while validating before deployment i run into
Failure Message: "System.LimitException: Too many SOQL queries: 101", Failure Stack Trace: "Trigger.getTradingAccountUPD: line 16, column 1"

in CaseViewExtensions.testController()

Failure Message: "System.LimitException: Too many SOQL queries: 101", Failure Stack Trace: "Trigger.getTradingAccountUPD: line 39, column 1"

in my test class getTradingAccountTest.test_getTradingAccount()

there must be still something, before the change with the sql inside the loop all was fine until a mass update
Any help would be really great
sfdcfoxsfdcfox
That's the point of the error; it shows that you've got a bug that will cause severe performance penalties when running against the data loader or an API integration. I don't see anything strictly wrong with your new code, so this is where it's going to get tricky. This is because the failure might be because of a different trigger or another query. The system only identifies the point where the limits are blown, not necessarily where the problem code is. You'll need to look at the test logs to determine which query/queries are being called the most, and then optimize the code that calls that query.
MoggyMoggy
the debug log is a brilliant idea, but another day of weekend work
as my testmethod pass but the validation on deploy fails.....i will report
aottaruaottaru
How did you resolve this issue? I am having exactly the same issue
andreas.wolfandreas.wolf
I would need to investigate deeper, but it looks like that i simply commented out the last two lines
es.Legacy_Account_Number__c='nope';
update es;

I also ensured that cases is not empty, so that the
Set<Contract> ta = new Set<Contract>.......
only fires if(!cases.isEmpty())

so the test class omits one update which seems to be the cause of the 101 limit
here the current testclass

@isTest(SeeAllData=true)
public class getTradingAccountTest {
static testMethod void test_getTradingAccount(){
Test.startTest();
        Case cs = new Case();
        cs.email__c = 'test.test@gmail.com';
        cs.Preferred_Reply_Method__c = 'Letter';
        cs.Post_Code__c = '95050';
        cs.Phone__c = '408123123';
        cs.First_Line_of_Address__c = 'test address';
        cs.Tracking_Job_Number__c = '1234567';
        cs.Consignment_Number__c = '24242323';
        cs.description= 'test';
        cs.Account_Number__c = '38J010W';
        insert cs;
       
        Case ds = new Case();
        ds.email__c = 'test.test@gmail.com';
        ds.Preferred_Reply_Method__c = 'Letter';
        ds.Post_Code__c = '95050';
        ds.Phone__c = '408123123';
        ds.First_Line_of_Address__c = 'test address';
        ds.Tracking_Job_Number__c = '1234567';
        ds.Consignment_Number__c = '24242323';
        ds.description= 'test';
        ds.Account_Number__c = 'test0001';
        insert ds;
       
        Case es = new Case();
        es.email__c = 'test.test@gmail.com';
        es.Preferred_Reply_Method__c = 'Letter';
        es.Post_Code__c = '95050';
        es.Phone__c = '408123123';
        es.First_Line_of_Address__c = 'test address';
        es.Tracking_Job_Number__c = '1234567';
        es.Consignment_Number__c = '24242323';
        es.description= 'test';
        es.Account_Number__c = 'rolle roe';
        insert es;
 
        NewCaseController x = new NewCaseController();
        x.trackAccount(cs);
        x.trackAccount(ds);
        x.trackAccount(es);
       
        es.Legacy_Account_Number__c = '38J010W';
        update es;
        es.Legacy_Account_Number__c=null;
        es.Trading_Account__c=null;
        es.Master_Trading_Account__c=null;
        es.Customer__c=null;
        update es;
        es.Legacy_Account_Number__c='test0001';
        update es;
        es.Legacy_Account_Number__c=null;
        es.Trading_Account__c=null;
        es.Master_Trading_Account__c=null;
        es.Customer__c=null;
        update es;
        //es.Legacy_Account_Number__c='nope';
        //update es;
       
        Test.stopTest();
}
}