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.
test.startTest()
andtest.stopTest()
(Keep your set up code out of the wrapped section). You can play with moving the test.startTest() and stopTest around to see if you can at least get the code in. This provides a new set of limits for the code that is wrapped in it. This may allow you to deploy you changes and help to resolve the offending code