Too many SOQL queries: 101 issue with Unit Test Methods

Question

We are facing couple of issue with Unit Test methods.

1. SOQL queries limit hits before "Test.StartTest()". We want to test a batch process and it needs to creates records for around 8 to 10 objects which has triggers. Because of that it is throwing limit exception before "Test.StartTest()".

Below is example of code.

@isTest
private class Test_Trigger_Contact
 {
     static testMethod void test1() 
     {        
       Student__c  studentToAdd; 
       List<Contact> contacts= new List<Contact>();  

      // To create Test data we are creating some test records of around 8-10 objects.
      // Internally it fires trigger and consumes diffrent SOQL queries, which cross 101 SOQL limit.
      // Because of this our test class fails before "Test.StartTest()"/"Test.StopTest()".
       for(integer i=0;i<101;i++)
         {
               contacts= [SELECT Id,Name FROM   Contact];

         }        
       test.startTest();
      // Do Some Unit Test for a batch process
      test.stopTest();
     }
 }

2. Another problem is, Salesforce is not resetting the Governor limit inside "Test.StartTest()" and "Test.StopTest()". Which is mentioned on below Salesforce issue.

https://success.salesforce.com/issues_view?id=a1p30000000T5R5AAK

With below example, you can reproduce this issue :

 @isTest
 private class Test_Trigger_Contact
 {
     static testMethod void test1() 
     {        
       Student__c  studentToAdd; 
       List<Contact> contacts= new List<Contact>();  

      // Below code is consuming 98 queries to generate Test Data
       for(integer i=0;i<98;i++)
         {
               contacts= [SELECT Id,Name FROM   Contact];

         }   
      // After that we are using "test.startTest()", to reset governer imit.
      // But it doesn't and throws 101 SOQL query error.
       test.startTest();
      // Do Some Unit Test for a batch process
       for(integer i=0;i<3;i++)
       {
               contacts= [SELECT Id,Name FROM   Contact];

         }  
      test.stopTest();
     }
 }

2nd issue will be fixed with Salesforce release as mentioned on above link.

I am not sure how can we fix 1st issue. Where we need to generate large data to test Batch Process. My understanding was, Salesforce calculates governor limits only inside "Test.startTest()" and "Test.stopTest()" for unit Test method and it doesn't calculates it while generating test data. Can someone please confirm my understanding regarding?

Thanks and Regards, Ashish Shukla

Answer 1

By using the Test.startTest() & Test.stopTest() you get 2X governer limits.

In nutshell you have 2 context.

1. out side of Test.startTest() & Test.stopTest() and
2. inside of Test.startTest() & Test.stopTest().

In both context you get separate limits.

To fix this issue what you will have to do is:

1. Optimize and see if you can reduce the queries in trigger
2. Reduce the number of objects you are inserting
3. You can skip unnecessary trigger execution by checking if trigger being called by Test method if(Test.isRunningTest())
4. in worst case you can use SeeAllData = ture

Answer 2

This issue (SOQL Limit not resetting in Test.StartTest() & Test.StopTest()) is now fixed and the details can be found on below link : https://success.salesforce.com/issues_view?id=a1p30000000T5R5AAK

Answer 3

I know this is an old post but I was struggling with "Too many SOQL" errors in my Apex test classes for a while as well and just recently/finally got a handle on it. Here is a simplified/paraphrased version of how I solved my "Too many SOQL" test code issues in case anyone else out there is still working through this and would prefer a clear example:

@IsTest
private class AccountTests{

    /* ---------------------------------------------
    1) Add one @testSetup method per test class to create the 
       data all other @IsTest methods in this class can use 
    ------------------------------------------------ */
    @testSetup
    static void GenerateAccountTestData(){
         // Add all possible tests records here

         // Account
         Account a = new Account();
         a.Name = 'ACME';
         a.Type = 'Prospect';
         // etc
         insert a;

         // Add any other records your tests methods will need:
         // Contacts, Orders, Products, whatever
    }

    /* ---------------------------------------------
       2) Add all your @IsTest test methods and 
       use the data created in the @testSetup method 
       for whatever update/alteration steps needed 
       in your tests 
    ------------------------------------------------ */
    @IsTest
    static void AccountTest1() {
        // Make sure to start and end your test methods with 
        // Test.startTest() / Test.stopTest() as these will
        // reset your governor/SOQL limits
        Test.startTest();

        // Use records from the GenerateTestData() method for tests
        Account testAccount = [SELECT Id, Name from Account LIMIT 1];

        // Do test stuff here
        System.assert(yourTestResult);
        Test.stopTest();
    }

    @IsTest
    static void AccountTest2() {
        // same structure as AccountTest1
    }
    @IsTest
    static void AccountTest3() {
        // same structure as AccountTest1
    }
    @IsTest
    static void AccountTest4() {
        // same structure as AccountTest1
    }

}

Like I said I just got over the hump w/this so maybe this isn't perfect - but it definitely got me out from under the "Too many SOQL" thing (knock on wood). If anyone knows of a better technique or ways to improve my approach please do leave a comment.

Related: https://stackoverflow.com/questions/12009698/salesforce-avoiding-governor-limits-in-test-classes-across-the-board

Answer 4

I have sometimes found that it would return this error when there are no records to be found (ironically). This is especially when I did not initialize the list or use SOQL for loops.

Ex - Where there are no Accounts in the org with Ids mentioned in the List 'accIds', I got the Too many SOQL queries error on running the test class.

    for(Account acc : [SELECT Id FROM Account WHERE Id IN: accIds]){
        //Some code
    }

    or

    List<Contact> contacts = [SELECT Id FROM Contact WHERE (some condition)];

Solution would be to debug the list separately and check if it returned any records. This could be just one of many reasons why it threw that error.

Answer 5

Something to add to AtulRajguru9's answer is that you should bulkify your assignment and the trigger as well if it hasn't been. What that means is take the SOQL statement out of the loop and get all the records you need in one statement. I can't tell with the code you've provided, but the simplest way to do this is to use dynamic queries to build a string finding each ID, as in Database.Query('select ID, name from Contact where ID = \'' + id1 + '\' or ID = \'' + id2...); (Of course, this would make more sense to be built in a loop). Similarly with DML, insert or update a whole list of records at once and make sure your triggers support it, don't run one update statement per loop iteration.