Too many SOQL queries: 101 - Test Class w. no queries / DML inside loops

Question

I have a trigger which contains several Ifs, populating my record's fields with different values, depending on which picklist value has been selected.

So I need to test Inserts & Updates of records with each value. Running this Test Class succeeds.

@isTest
public class WE_OppUpdatesTest {

    static testMethod void testNewMRFs(){

        Profile p1 = [SELECT Id FROM Profile WHERE Name = 'System Administrator - Wex Europe'];

        User[] userList = new User[]{};
            User u = new User();

        u.FirstName = 'Alex';
        u.LastName = 'Sherwood';
        u.Email = '[email protected]';
        u.Username = '[email protected]';
        u.Alias = 'astest';
        u.ProfileId = p1.Id;
        u.TimeZoneSidKey    = 'America/Denver';
        u.LocaleSidKey      = 'en_US';
        u.EmailEncodingKey  = 'UTF-8';
        u.LanguageLocaleKey = 'en_US';
        userList.add(u);

        insert userList;

        System.runas(u){

            WEID__c[] csList = new WEID__c[]{};
                WEID__c cs = New WEID__c();
            cs.Name = 'CP Virtual Prepaid MC';
            cs.WEOppId__c = '012700000005qif';

            csList.add(cs);

            insert csList;

            Target__c[] targetList = new Target__c[]{};
                Target__c t = new Target__c();

            t.Name = 'Jun Test';
            t.OwnerId = u.Id;
            t.Salesperson__c = u.Id;
            t.Team__c = 'Virtual Sales EU';
            t.Year_End_Date__c = Date.newInstance(2015,12,31);
            t.Target__c = 100000;
            t.Month_End_Date__c = Date.newInstance(2015,06,30);
            targetList.add(t);

            Target__c t1 = new Target__c();
            t1.Name = 'Jul Test';
            t1.OwnerId = u.Id;
            t1.Salesperson__c = u.Id;
            t1.Team__c = 'Virtual Sales EU';
            t1.Year_End_Date__c = Date.newInstance(2015,12,31);
            t1.Target__c = 100000;
            t1.Month_End_Date__c = Date.newInstance(2015,07,30);
            targetList.add(t1);

            Target__c t2 = new Target__c();
            t2.Name = 'Jul 16 Test';
            t2.OwnerId = u.Id;
            t2.Salesperson__c = u.Id;
            t2.Team__c = 'Virtual Sales EU';
            t2.Year_End_Date__c = Date.newInstance(2016,12,31);
            t2.Target__c = 100000;
            t2.Month_End_Date__c = Date.newInstance(2016,07,30);
            targetList.add(t2);

            insert targetList;

            List<Opportunity> opportunitys = new List<Opportunity>();

            for (Integer i = 0; i < 50; i++) {

                Opportunity o = new Opportunity();
                o.name = 'Test Opp' + i;
                o.RecordTypeId = '012700000005qif';
                o.OwnerId = u.Id;

                o.StageName = '1) Suspect';
                o.CloseDate = date.newInstance(2015,06,15);
                o.Ramp_Profile__c = 'Standard';

                o.Monthly_Txn_Revenue_POS__c = 0;
                o.Annual_transaction_volume__c = 1000;
                o.Credit_Limit__c = 100.00;
                o.Date_Credit_Limit_Approved__c = date.newInstance(1901, 01, 01);
                o.Payment_Terms_Days__c = 5;
                o.Billing_Cycle__c = 'Daily';
                o.Annual_Settlement_Value__c = 100.00;
                o.Average_transaction_Value_POS__c = 1.00;

                opportunitys.add(o);
            }
            insert opportunitys;

            //UPDATES
            for(Opportunity upOppsDate : opportunitys){
                upOppsDate.CloseDate = date.newInstance(2016,07,15);
                upOppsDate.Ramp_Profile__c = 'Immediate';
                upOppsDate.Implementation_Revenue__c = null;
                upOppsDate.FSR_End_Date__c = null;
                upOppsDate.Implementation_Revenue_as_a_of_FSR__c = null;
            }
            update opportunitys;

            for(Opportunity upOppsRMP : opportunitys){
                upOppsRMP.Ramp_Profile__c = 'Medium';
                upOppsRMP.Implementation_Revenue__c = null;
                upOppsRMP.FSR_End_Date__c = null;
                upOppsRMP.Implementation_Revenue_as_a_of_FSR__c = null;
            }
            update opportunitys;

            for(Opportunity upOppsRMP : opportunitys){
                upOppsRMP.Ramp_Profile__c = 'Extended';
                upOppsRMP.Implementation_Revenue__c = null;
                upOppsRMP.FSR_End_Date__c = null;
                upOppsRMP.Implementation_Revenue_as_a_of_FSR__c = null;
            }
            update opportunitys;

        }
    }
}

But as soon as I add an extra DML statement to my Test Class (either Insert or Update) for example -

List<Opportunity> immOpportunitys = new List<Opportunity>();

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

                Opportunity o = new Opportunity();
                o.name = 'Test Opp' + i;
                o.RecordTypeId = '012700000005qif';
                o.OwnerId = u.Id;
                o.Ramp_Profile__c = 'Immediate';
                o.StageName = '1) Suspect';
                o.CloseDate = date.newInstance(2015,06,15);
                o.Monthly_Txn_Revenue_POS__c = 0;
                o.Annual_transaction_volume__c = 1000;
                o.Credit_Limit__c = 100.00;
                o.Date_Credit_Limit_Approved__c = date.newInstance(1901, 01, 01);
                o.Payment_Terms_Days__c = 5;
                o.Billing_Cycle__c = 'Daily';
                //cover Revenue Values Required validation rule
                o.Annual_Settlement_Value__c = 100.00;
                o.Average_transaction_Value_POS__c = 1.00;

                immOpportunitys.add(o);
            }
            insert immOpportunitys;

It causes the error

System.LimitException: Too many SOQL queries: 101

So it looks like each time I Insert / Update a new set of records, any resulting SOQL queries from other code are added to a governor limit which is being totaled for this class, when in reality only one batch of records could be Inserted / Updated at once. Changing the number of records in the lists, which are being changed, doesn't seem to make any difference.

Surely I should be able to Insert / Update multiple lists of records in the same Test Class, with separate governor limits for each, to test the different picklist values?

Note - the trigger which I'm testing doesn't contain any SOQL queries so to keep the question short, I haven't posted it.
My debug log size is too large to filter in the Developer Console, due to a (hopefully) unrelated issue but there's no obvious signs of an SOQL query in a loop, when I search it.

Answer 1

The reason for your limit exception is probably that you're trying to do to many operations in one transaction.

You need to know that all governor limits are counted against one transaction. Transaction is not one class execution - it is the whole stack of calls.

Example:

• You insert opportunity // Transaction begins
• beforeInsert trigger is executed (let's say it has 1 SOQL query inside - limit of soql for transaction equals 1 right now)
• afterInsert trigger is executed (let's say it has 2 SQOL and update of related record - Account)
• account beforeInsert is executed...
• account afterInsert
• End of transaction (success if no error, and governor limits are not exceeded)

So if you have your test class - in one method you're making multiple inserts - it's all counted as one transaction.

Best practice for test class development is to separate it to multiple methods - each testing one thing. This way each test method is executed separately and have separate governor limits. Also good practice is to include method with @testSetup method when you will insert all records required for other test methods.

@isTest
public class WE_OppUpdatesTest {

   @testSetup
   static void dataSetup() {
      ...
      insert user;
   }

   static void additionalDataSetup() {
      User u = [SELECT Id FROM User WHERE Name = 'yourname' LIMIT 1];
      System.runAs(u) {
         ... insert all additional records
      }
   }

   static testMethod void test_something() {
      additionalDataSetup();
      ...
   }

   static testMethod void test_somethingElse() {
      additionalDataSetup();
      ...
   }
}

Hope this helps.

Answer 2

You should check Opportunity triggers. Also, instead of testing all cases in a single test method, try to break them in different test methods.

You should check all SOQLs in Opportunity triggers and also all triggers which run during the update of an Opportunity.

Try to decrease following record counts:

for (Integer i = 0; i < 50; i++) 

Start from one or two iterations only.

for (Integer i = 0; i < 2; i++) 
{
    ......
}

Otherwise, you may encounter issues caused by SOQL queries in for loops OR recursive triggers.