Unable to deploy anything due to System.Exception: Too many SOQL queries

Question

I've read through as many of the threads here on this as I can but haven't been able to find a solution to my specific problem.

Over the past week we've been unable to deploy any code that impacts the Account object - test execution halts at "AccountTrgUtil_Test.NextActivityTest(), Details: System.LimitException: Too many SOQL queries: 101 Trigger.AccountTeam: line 58, column 1".

The trigger mentioned is as follows:

trigger AccountTeam on Account (after update) {

Integer newcnt = 0;
Integer newcnt0 = 0;
List<Id> accOwnerIds = new List<Id>();
AccountTeamMember[] newmembers = new AccountTeamMember[]{};
//list of new team members to add
AccountShare[] newShare = new AccountShare[]{};
// Get users owning updated records
for(Account acc: trigger.newMap.values())
{
    accOwnerIds.add(acc.ownerid);
}

Map<id,user> mpuser=new Map<id,user>([select id, name, profile.name from user where id in :accOwnerIds]);
for(Account newAcc: trigger.newMap.Values()){

    String newOwnerProfile = (mpuser.get(trigger.newMap.get(newAcc.id).ownerid).profile.name).trim();
    if (trigger.old[0].OwnerId != newAcc.OwnerId
        && (newOwnerProfile == 'Acquire Career Champion' || newOwnerProfile == 'Eddi Edvisor')
        && newAcc.Opportunity_Stage__c != 'Course Deffered Requested' 
        && newAcc.Opportunity_Stage__c != 'Course Deffered Confirmed' 
        && newAcc.Opportunity_Stage__c != 'Cancellation Requested' 
        && newAcc.Opportunity_Stage__c != 'Cancellation Confirmed') {

            System.Debug('New team member to be added');
            AccountTeamMember Teammemberad=new AccountTeamMember();
            Teammemberad.AccountId = newAcc.id;
            Teammemberad.UserId = newAcc.OwnerId;
            if (newOwnerProfile == 'Acquire Career Champion') {
                Teammemberad.TeamMemberRole = 'Career Champion';
                System.Debug('Career Champion team member added: ' + newAcc.OwnerId);
            }
            else if (newOwnerProfile == 'Eddi Edvisor') {
                Teammemberad.TeamMemberRole = 'Eddi Trainer / Assessor';
                System.Debug('Eddi Trainer / Assessor team member added: ' + newAcc.OwnerId);
            }
            newmembers.add(Teammemberad);
        }
    } 
Database.SaveResult[] lsr = Database.insert(newmembers,false);
//insert any valid members then add their share entry if they were successfully added Integer newcnt=0;
for(Database.SaveResult sr:lsr) 
{
    if(!sr.isSuccess()) {
        Database.Error emsg =sr.getErrors()[0];

        system.debug('\n\nERROR ADDING TEAM MEMBER:'+emsg);
    } else {
        //Create sharing for new account member
        newShare.add(new AccountShare(UserOrGroupId=newmembers[newcnt].UserId, AccountId=newmembers[newcnt].Accountid, AccountAccessLevel='All',OpportunityAccessLevel='Read'));
    }
    newcnt++;
}

//Give outgoing Owner edit access to Account
try {
    AccountShare oldOwnerShare = [select AccountId, AccountAccessLevel, UserOrGroupId from AccountShare where AccountId =: trigger.old[0].Id and UserOrGroupId =: trigger.old[0].OwnerId limit 1];
    oldOwnerShare.AccountAccessLevel = 'Edit';
    system.debug('Old owner ' + trigger.old[0].OwnerId + ' accountshare updated to write'); 
    update oldOwnerShare;
} catch(exception ex){
    system.debug('ex ############:' + ex);
} 
Database.SaveResult[] lsr0 =Database.insert(newShare,false);
//insert the new shares Integer newcnt0=0;
for(Database.SaveResult sr0:lsr0) {
    if(!sr0.isSuccess()) {
        Database.Error emsg0 = sr0.getErrors()[0];
        system.debug('\n\nERROR ADDING SHARING:'+newShare[newcnt0]+'::'+emsg0); 
    } 
    newcnt0++; 
}

}

There aren't any SOQL queries or DML commands in for loops in this trigger, so I don't think it's the culprit - more that classes/triggers involved in related test execution (of the AccountTrgUtil_test class which was developed by a previous partner) are inefficiently running SOQL queries.

I have looked through a number of other triggers and classes for SOQL queries in for loops and fixed a few offending ones - but I can't even deploy these to production without hitting the SOQL limit. I've also seen a suggestion to check for Test.StartTest and StopTest flags in test classes, and have added those to the AccountTrgUtil_test class methods, successfully deployed it, but the problem persists.

I took the advice here and ran the test class AccountTrgUtil_test mentioned in the error, and generated the debug log.

The resulting debug log runs 25390 lines and contains 259 "SOQL_EXECUTE_BEGIN" statements. There are 735 "Number of SOQL Queries" summary statements, most of which are very low, but there is a series of statements that quote numbers closer and closer to 100 ending with "Number of SOQL queries: 98 out of 100 ******* CLOSE TO LIMIT". I'm not able to see a pattern of what method is triggering the queries that are cumulating in this way, but there's a lot there and I'm not really sure what I'm looking for.

This is a fairly complex system with a varied implementation partner history, so there's a lot of code I'm unfamiliar with that could be causing this.

I guess I'm just after suggestions on how to troubleshoot this? Any advice greatly appreciated, we're at a standstill until we can find a solution.

Edit: Here's the test class I believe is at the center of a lot of the SOQL usage.

@isTest

public class AccountTrgUtil_Test{

public static testmethod void RetentionTest(){

    Profile profile = [SELECT Id FROM Profile WHERE Name='Acquire Career Advisor']; 

    User user = new User(Alias = 'standt', Email='test.user@testorg.com', 
    EmailEncodingKey='UTF-8', LastName='Testing', LanguageLocaleKey='en_US', 
    LocaleSidKey='en_US', ProfileId = profile.Id, 
    TimeZoneSidKey='America/Los_Angeles', UserName='test.acquireLearning@testorg.com');
    insert user;

    RecordType recType = [select id from RecordType where DeveloperName = 'PersonAccount' LIMIT 1];

    Test.startTest();

    Account Acc = new Account();
    Acc.recordtypeid = recType.id;
    Acc.LastName='Test';
    Acc.Opportunity_Stage__c = 'Course in Progress';
    //Acc.Previous_record_ownerId__c = '00590000002DA6M';
    //Acc.OwnerId = '00590000001T5iA';
    //Acc.Retension_Specialist_ID__c = '00590000002DEUfAAO';
    Acc.Retension_Specialist_Name__c = 'Retention';
    //Acc.OwnerId = Acc.Previous_record_ownerId__c;  
    insert Acc ;

    AccountTrgUtil.flag = false;

    Opportunity Opp = new Opportunity();
    //Opp.Account = Acc.Name;
    Opp.OwnerId = user.id;
    Opp.Name = 'Oppor';
    Opp.accountId = Acc.id;
    opp.StageName = 'Enrolled - Confirmed';
    Opp.CloseDate = system.today();
    Opp.X1st_census_date__c = system.today();
    Opp.X2nd_census_date__c = system.today();
    Opp.Cancelation_Reasons__c = 'Unaware of enrolment';
    Opp.Source_of_Cancelation__c = 'Career Champion';
    Opp.Enrolled_Awaiting_ID__c = false;
    Opp.Enrolled_Awaiting_VET_Fee__c = false;
    Opp.Enrolled_Incorrect_Course__c = false;
    insert Opp ;

    Acc.Recent_Closed_Opportunity_Id__c = opp.id;
    Acc.Opportunity_Stage__c = 'Cancellation Requested';
    Acc.Opportunity_Cancellation_Reason__c = 'Unaware of enrolment';
    Acc.Source_of_Cancelation__c = 'Career Champion';
    update Acc;


    System.runAs(user) {
        Opp.OwnerId = user.id;
        Opp.StageName = 'Cancellation Requested';
        update Opp;    
    }

     test.stopTest();
}

public static testmethod void CampaingMember(){

Profile profilecamp = [SELECT Id, Name FROM Profile WHERE Name=: 'Acquire Career Champion'];

User userdetails = new User(Alias = 'standt1', Email='test.user1@testorg.com', 
    EmailEncodingKey='UTF-8', LastName='Testing', LanguageLocaleKey='en_US', 
    LocaleSidKey='en_US',ProfileId = profilecamp.Id,
    TimeZoneSidKey='America/Los_Angeles', UserName='test.acquireLearning@testorg.com');
    insert userdetails;
    Test.startTest();

    Account Acc = new Account();
    Acc.LastName = 'test';
    insert Acc;

 /*   Lead Camplead = new Lead();
    Camplead.Lastname= 'Test';
    Camplead.Firstname= 'Lead';
    Camplead.Status = 'New';
    insert Camplead;

    CampaignMember CampMem = new CampaignMember();
    CampMem.Status = 'Sent';
    CampMem.CampaignId =Camplead.Id ;
    insert CampMem;*/


    Campaign Camp = new Campaign ();
    //Camp.id = OwnerId;
    //Camp.Id= Camplead.Id;
    Camp.name = 'Student Welcome Email';
    insert Camp;

    Acc.OwnerId = userdetails.Id;
    Update Acc;

}

 public static testmethod void VentureCampaingMember(){

    Test.startTest();

    Account Acct= new Account();
    Acct.LastName = 'test';
    Acct.Opportunity_Stage__c = 'Course in Progress';
    Acct.Is_Venture_Campaign_Member_Created__c= false;
   //Acct.Education_Provider_Name__c='Careers Australia';
    //Acct.Course_Rising_Star_program_participant__c = true;


    insert Acct;


 /*   Lead Camplead = new Lead();
    Camplead.Lastname= 'Test';
    Camplead.Firstname= 'Lead';
    Camplead.Status = 'New';
    insert Camplead;

    CampaignMember CampMem = new CampaignMember();
    CampMem.Status = 'Sent';
    CampMem.CampaignId =Camplead.Id ;
    insert CampMem;*/


    Campaign Camp1 = new Campaign ();
    //Camp.id = OwnerId;
    //Camp.Id= Camplead.Id;
    Camp1.name = 'Venture Email';


    // Camp1.Education_Provider_Name__c = Acct.Education_Provider_Name__c;
    insert Camp1;
    Camp1.Status = 'Sent';
    update Camp1;


}


public static testmethod void NextActivityTest(){
    Test.startTest();
    Account acc = new Account();
    acc.X01_Welcome_Call_And_Email__c= '0';
    acc.Next_Activity__c='02 Induction Call';
    acc.X02_Induction_Call__c='0';
    acc.X03_Goal_Setting__c='0';
    acc.X04_Personality_Profiling__c='0';
    acc.X05_Social_Media_Strategy__c='0';
    acc.X06_LinkedIn_Resume_Cover_Letter__c='0';
    acc.X07_Interview_Techniques_And_Tips__c='0';
    acc.X08_Dress_for_Success__c='0';
    acc.X09_Job_Search_Techniques__c='0';
    acc.X10_Hidden_Job_Approach__c='0';
    acc.X11_Keeping_a_Job__c='0';
    acc.X12_Internships_or_Acquire_Experiences__c='0';
    acc.X13_Role_Play_for_Interview__c='0';
    acc.Industry_Preferences__c='Construction';
    acc.Sub_Category_1__c='Estimating';
    acc.RecordTypeId = '01290000000XDGF';
    acc.Name='test';
    //acc.Id='001N000000E5P4I';
    insert acc;

    acc.X01_Welcome_Call_And_Email__c= '0';
    update acc;

    acc.X01_Welcome_Call_And_Email__c= '1';
    acc.X02_Induction_Call__c='0';
    update acc;

    Careers__c crs = new Careers__c();
    crs.Student_Name__c =acc.Id;
    insert crs;

    acc.X01_Welcome_Call_And_Email__c= '1';
    acc.X02_Induction_Call__c='1';
    acc.X03_Goal_Setting__c='0';
    update acc;



    acc.X01_Welcome_Call_And_Email__c= '1';
    acc.X02_Induction_Call__c='1';
    acc.X03_Goal_Setting__c='1';
    acc.X04_Personality_Profiling__c='1';
    acc.X05_Social_Media_Strategy__c='1';
    acc.X06_LinkedIn_Resume_Cover_Letter__c='1';
    acc.X07_Interview_Techniques_And_Tips__c='1';
    acc.X08_Dress_for_Success__c='1';
    acc.X09_Job_Search_Techniques__c='0';
    update acc;

    acc.X01_Welcome_Call_And_Email__c= '1';
    acc.X02_Induction_Call__c='1';
    acc.X03_Goal_Setting__c='1';
    acc.X04_Personality_Profiling__c='1';
    acc.X05_Social_Media_Strategy__c='1';
    acc.X06_LinkedIn_Resume_Cover_Letter__c='1';
    acc.X07_Interview_Techniques_And_Tips__c='1';
    acc.X08_Dress_for_Success__c='1';
    acc.X09_Job_Search_Techniques__c='1';
    acc.X10_Hidden_Job_Approach__c='0';
    update acc;

    acc.X01_Welcome_Call_And_Email__c= '1';
    acc.X02_Induction_Call__c='1';
    acc.X03_Goal_Setting__c='1';
    acc.X04_Personality_Profiling__c='1';
    acc.X05_Social_Media_Strategy__c='1';
    acc.X06_LinkedIn_Resume_Cover_Letter__c='1';
    acc.X07_Interview_Techniques_And_Tips__c='1';
    acc.X08_Dress_for_Success__c='1';
    acc.X09_Job_Search_Techniques__c='1';
    acc.X10_Hidden_Job_Approach__c='1';
    acc.X11_Keeping_a_Job__c='0';
    update acc;

    acc.X01_Welcome_Call_And_Email__c= '1';
    acc.X02_Induction_Call__c='1';
    acc.X03_Goal_Setting__c='1';
    acc.X04_Personality_Profiling__c='1';
    acc.X05_Social_Media_Strategy__c='1';
    acc.X06_LinkedIn_Resume_Cover_Letter__c='1';
    acc.X07_Interview_Techniques_And_Tips__c='1';
    acc.X08_Dress_for_Success__c='1';
    acc.X09_Job_Search_Techniques__c='1';
    acc.X10_Hidden_Job_Approach__c='1';
    acc.X11_Keeping_a_Job__c='1';
    acc.X12_Internships_or_Acquire_Experiences__c='0';
    update acc;

    acc.X01_Welcome_Call_And_Email__c= '1';
    acc.X02_Induction_Call__c='1';
    acc.X03_Goal_Setting__c='1';
    acc.X04_Personality_Profiling__c='1';
    acc.X05_Social_Media_Strategy__c='1';
    acc.X06_LinkedIn_Resume_Cover_Letter__c='1';
    acc.X07_Interview_Techniques_And_Tips__c='1';
    acc.X08_Dress_for_Success__c='1';
    acc.X09_Job_Search_Techniques__c='1';
    acc.X10_Hidden_Job_Approach__c='1';
    acc.X11_Keeping_a_Job__c='1';
    acc.X12_Internships_or_Acquire_Experiences__c='1';
    acc.X13_Role_Play_for_Interview__c='0';
    update acc;

    acc.X01_Welcome_Call_And_Email__c= '1';
    acc.X02_Induction_Call__c='1';
    acc.X03_Goal_Setting__c='1';
    acc.X04_Personality_Profiling__c='1';
    acc.X05_Social_Media_Strategy__c='1';
    acc.X06_LinkedIn_Resume_Cover_Letter__c='1';
    acc.X07_Interview_Techniques_And_Tips__c='1';
    acc.X08_Dress_for_Success__c='1';
    acc.X09_Job_Search_Techniques__c='1';
    acc.X10_Hidden_Job_Approach__c='1';
    acc.X11_Keeping_a_Job__c='1';
    acc.X12_Internships_or_Acquire_Experiences__c='1';
    acc.X13_Role_Play_for_Interview__c='1';
    acc.Next_Activity__c = 'No Activity';
    update acc;

    test.stopTest();
}

public static testMethod void updateOppOwnerTest(){
    Profile hunterProfile = [SELECT Id, Name FROM Profile WHERE Name=: 'Acquire Career Hunter']; 

    Profile shortlisterProfile = [SELECT Id, Name FROM Profile WHERE Name=: 'Acquire Career Qualifiers']; 

    User user1 = new User(Alias = 'standt1', Email='test.user1@testorg.com', 
    EmailEncodingKey='UTF-8', LastName='Testing1', LanguageLocaleKey='en_US', 
    LocaleSidKey='en_US', ProfileId = hunterProfile.Id, 
    TimeZoneSidKey='America/Los_Angeles', UserName='test.acquireLearning1@testorg.com');
    insert user1;

    User user2 = new User(Alias = 'standt2', Email='test.user2@testorg.com', 
    EmailEncodingKey='UTF-8', LastName='Testing2', LanguageLocaleKey='en_US', 
    LocaleSidKey='en_US', ProfileId = shortlisterProfile.Id, 
    TimeZoneSidKey='America/Los_Angeles', UserName='test.acquireLearning2@testorg.com');
    insert user2;


    RecordType accRecType = [Select id From RecordType where sObjectType = 'Account' AND DeveloperName = 'PersonAccount' LIMIT 1];

    RecordType oppRecType = [Select id From RecordType where sObjectType = 'Opportunity' AND DeveloperName = 'Employer' LIMIT 1];

    Account acc = new Account();
    acc.recordtypeid = accRecType.id;
    acc.LastName='Test';
    acc.Opportunity_Stage__c = 'Course in Progress';
    insert acc ;

    Opportunity opp = new Opportunity();
    opp.recordtypeid = oppRecType.id;
    opp.Name = 'Oppor';
    opp.accountId = Acc.id;
    opp.StageName = 'Job Placed';
    opp.CloseDate = system.today();
    opp.X1st_census_date__c = system.today();
    opp.X2nd_census_date__c = system.today();
    opp.Career_Hunter_Owner_ID__c = user1.id;
    insert opp ;

    acc.OwnerId = user2.id;
    update acc;

    opp.OwnerId = user2.id;
    update opp;

    Test.startTest();

    System.runAs(user2){
        opp.StageName = 'Job Shortlisted';
        update opp;
    }   

    Test.stopTest();
} 

}

As noted below, I achieved a temporary fix by removing some of the repeated update DML commands (although at the cost of test quality) and reducing scope of the starttest and stoptest flags. But a day later we have hit the SOQL limit again with a new deployment, so the problem remains unsolved.

Answer 1

This is a fairly complex system with a varied implementation partner history, so there's a lot of code I'm unfamiliar with that could be causing this.

I loved this remark. I feel your pain

@Eric has a good suggestion with Test.startTest() that sometimes works but failing that, here are some other things to try:

First of all, focus on the testmethods that fail at Too Many SOQL

1. See if you can break the testmethod into two testmethods - perhaps it is testing multiple scenarios that cause the excessive SOQL
2. Verify that the testmethods are not using SeeAllData=true or are running at V23 or earlier. This means that when you deploy, you are fetching PROD dataset results that in turn might lead to excessive SOQL given the testmethod use case
3. Verify that test data is being inserted in bulk rather than separate DML for the same SObject (assuming that using Test.startTest() doesn't resolve the too many SOQL during the mocking of test data).
4. Look through the debug log to see if reference data is being fetched over and over again - like calls to get User or Profile - these can be replaced by a Utility class/method that uses a static variable and fetches this reference data only once into a map, and then fetches from the map on subsequent requests
5. Look for workflows with field updates - these will cause the triggers to refire - this can help explain the debug log. You may need static variables to prevent repeating a trigger from executing during a workflow field update given application use case.
6. Same as #5 except roll-up summary fields
7. Cascading triggers - an after update/insert for SObject X invokes before/after insert/update triggers on related objects

And of course, individual triggers and their helper classes must not do SOQL inside of for loops. This is usually the biggest culprit but I suspect you have already been down this route - only the debug log and/or developer Console logs/profiles can tell you for sure