You need to sign in to do that
Don't have an account?
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(); } }
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..
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.
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
I will check them
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
as my testmethod pass but the validation on deploy fails.....i will report
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();
}
}