System.LimitException: Too many SOQL queries: 101 when deployng

Question

I have faced with the annoing error: System.LimitException: Too many SOQL queries: 101. I checked all that may hits the governor errors but still no luck.... Can you point me where in the code hits the limits? The error occures only when deployng in to production:

Class Name: TestITAssetProcessingBatchClass MethodName: testBatch Error Msg: System.LimitException: Too many SOQL queries: 101 Stack Trace: Trigger.CashFlowIntegration: line 10, column 1

This is TestITAssetProcessingBatchClass:

@isTest(SeeAllData=false)
class TestITAssetProcessingBatchClass {

static testmethod void testBatch() {

Test.startTest();
   Account acc = new Account();
   acc.Name = 'Test Account';
   insert acc; 

List<IT_Asset__c> st=new List<IT_Asset__c>(); 
    for(integer i = 0;  i < 25; i++){
        IT_Asset__c it = new IT_Asset__c();
            it.Account__c = acc.Id; 
            it.FrstDayOfMnth__c = true; 
            it.Quantity__c = 4;
        st.add(it);
    }
    insert st;

    Database.BatchableContext bc;
    ITAssetProcessingBatch obj=new ITAssetProcessingBatch();
    obj.query = 'Select id From IT_Asset__c Where Quantity__c = 4 Limit 100';
    obj.start(BC);
    obj.execute(BC, st);
    obj.finish(BC);

    System.assertEquals(25, Database.query(obj.query).size() );
Test.stopTest();
}
}

And This is my trigger CashFlowIntegration:

trigger CashFlowIntegration on Cash_Flow__c (before insert, before update ) {

Set<String> AccNum = new Set<String>(); 
for (Cash_Flow__c CF : trigger.new) { 
    AccNum.add(CF.AccNumber__c); } 

Map<String, Account_Number__c> AccountNumbers = new Map<String, Account_Number__c>();

List <Account_Number__c> rec = [ SELECT Account_No__c, Account__c FROM Account_Number__c WHERE Account_No__c IN :AccNum LIMIT 1];     
List <Account_Number__c> rec2 = [ SELECT Account_Number_Old__c, Account__c FROM Account_Number__c WHERE Account_Number_Old__c IN :AccNum LIMIT 1];
List <Account_Number__c> rec3 = [ SELECT Acronym__c, Account__c FROM Account_NUmber__c WHERE Acronym__c IN :AccNum LIMIT 1];
if (rec != NULL){
    for (Account_Number__c record : rec){ 
         AccountNumbers.put(record.Account_No__c, record);
        }
}
if (rec2 != NULL){
      for (Account_Number__c record2 : rec2){       
          AccountNumbers.put(record2.Account_Number_Old__c, record2);
        }  
}
if (rec3 != NULL){
    for (Account_Number__c record3 : rec3) {
        AccountNumbers.put(record3.Acronym__c, record3);
    }
}

    for (Cash_Flow__c CF : Trigger.new){
        Account_Number__c siteAccount = AccountNumbers.get(CF.AccNumber__c);
        Id parentId = (siteAccount == null) ? null : siteAccount.Id;
        Id accId = (siteAccount == null) ? null :siteAccount.Account__c;
        CF.Amount__c = -1*CF.Amount__c;
        CF.Account_Number__c = parentId;
        CF.Account__c = accId;
        if (accId == NULL){
            CF.Account__c = '001b000000r63RN';//'0017E000009c6QB';
        }
    }
 }

Answer 1

The problem is that the ITAssetCashFlowCreate trigger and CashFlowIntegration are not bulk safe. The ITAsset trigger upserts CashFlow records inside the for loop. So when you update the list of 25 ITAssets (presumably occurs in the batch), you call the upsert operation 25 times. The first time on one record, the second time on two, etc. Thus the loop in the CashFlowIntegration trigger will run 325 times. You have three SOQL triggers inside that loop. Thus an operation updating the 25 ITAssets will cause 975 SOQL queries to run.

Answer 2

Seems like you have error at below line

insert acc

Mostly there is trigger on account object.

If there are already 100 queries and you just use 1 query in account trigger then you will get this kind of error.

Check were most of queries are used and try to reduce the queries used.

The another thing that you can do is use test.starttest() and test.stoptest() which will give you additional 100 queries in test context.

Answer 3

Move this line

Test.startTest();

to after

insert st;

Your startTest should be after all the inserts in the test class. It should be used while calling the actual class/method. Below code should fix it.

/* All inserts.  
 .....Data setup 
*/
Test.startTest();
    Database.BatchableContext bc;
    ITAssetProcessingBatch obj=new ITAssetProcessingBatch();
     /* 
    ......
     */
    System.assertEquals(25, Database.query(obj.query).size() );
Test.stopTest();

EDIT:

Also, I see you are calling start(), execute(), finish() methods in test classes. These are not required. Remove them. It should be

String query = 'Select id From IT_Asset__c Where Quantity__c = 4 Limit 100';

ITAssetProcessingBatch obj=new ITAssetProcessingBatch(query);
Database.executeBatch(obj);

Answer 4

Thanks for all!

The problem was in the code:

List<IT_Asset__c> st=new List<IT_Asset__c>(); 
for(integer i = 0;  i < 25; i++){
    IT_Asset__c it = new IT_Asset__c();
        it.Account__c = acc.Id; 
        it.FrstDayOfMnth__c = true; 
        it.Quantity__c = 4;
    st.add(it);
}
insert st;

This is really creates the 25 records and another trigger fires for all of them so it cause the error "Limit 101".

So I change the code line

for(integer i = 0;  i < 25; i++){

to

for(integer i = 0;  i < 1; i++){

and all my tests pass.

Answer 5

I make the research and find some stack trace... but didn't know why it is bring errors...

1) The error occurs then the new triggers is created. and point on batch process that fires another trigger to create the cash flow record.

2) When i tried to debug i found that my trigger runs somelike as in for loop and points me on that line:

List <Account_Number__c> rec2 = [ SELECT Account_Number_Old__c, Account__c FROM Account_Number__c WHERE Account_Number_Old__c IN :AccNum LIMIT 1];

3) And this is the 2nd trigger that runs after batch completes (I think the problem is here):

Trigger ITAssetCashFlowCreate on IT_Asset__c (before update)
{
 List<Cash_Flow__c> sub=new List<Cash_Flow__c>();
 for(IT_Asset__c it : Trigger.new)
 {
       if(it.FrstDayOfMnth__c == true)
       {
               Cash_Flow__c cf=new Cash_Flow__c();
               cf.CurrencyIsoCode=it.CurrencyIsoCode;
               cf.Date__c=System.today();
               cf.IT_Asset__c=it.Id;
               cf.RecordTypeId='012b0000000UOay';
               cf.Account__c=it.Account__c;
               cf.Amount__c=-1*it.Total_Asset_Cost_mth__c;
               cf.Cost_Category__c='IT Cost';
               cf.Business_Line_picklist__c=it.Business_Line__c;
               cf.Cost_Center__c=it.Cost_Center_new__c;
               cf.Cost_Line__c=it.Cost_Line_new__c;
               cf.IT_Code__c=it.IT_Code_lookup__c;
               cf.Vendor__c=it.Vendor__c;
               cf.BCS_Entity__c=it.BCS_Entity__c;
               cf.Comment__c='Monthly cost autogenerated';

               //add other fields of subject here and add volunteer values in that.

               sub.add(cf);
        }
        if(sub.size()>0)
        upsert sub;
     if(it.FrstDayOfMnth__c == true){
            it.FrstDayOfMnth__c=false;
     }
 }
}