You need to sign in to do that
Don't have an account?
Too Many SOQL Queries Error
public class PopulateCorporateFields {
public List<Corporate_Account_List_Assignment__c> calist { set; get; }
public PopulateCorporateFields(ApexPages.StandardController controller) {
}
public void updateFields()
{
calist = [SELECT Account_Name__c,Corporate_Partnership_Director_1__c,Corporate_Partnership_Director_2__c,Corporate_Partnership_Director_3__c,Corporate_Partnership_Director_4__c,Corporate_Partnership_Director_5__c,Marketing_Solutions_1__c, Marketing_Solutions_2__c, Marketing_Solutions_3__c, Media_Services_1__c, Media_Services_2__c, Media_Services_3__c,Digital_Manager_1__c,Digital_Manager_2__c,Digital_Manager_3__c,Digital_Manager_4__c,Digital_Manager_5__c, Id,Name,Ultimate_Parent_Name__c FROM Corporate_Account_List_Assignment__c];
for (Corporate_Account_List_Assignment__c ca : calist)
{
ca.Corporate_Partnership_Director_1__c = null;
ca.Corporate_Partnership_Director_2__c = null;
ca.Corporate_Partnership_Director_3__c = null;
ca.Corporate_Partnership_Director_4__c = null;
ca.Corporate_Partnership_Director_5__c = null;
ca.Digital_Manager_1__c = null;
ca.Digital_Manager_2__c = null;
ca.Digital_Manager_3__c = null;
ca.Digital_Manager_4__c = null;
ca.Digital_Manager_5__c = null;
ca.Marketing_Solutions_1__c = null;
ca.Marketing_Solutions_2__c = null;
ca.Marketing_Solutions_3__c = null;
ca.Media_Services_1__c = null;
ca.Media_Services_2__c = null;
ca.Media_Services_3__c = null;
System.debug('******ID********'+ca.Id);
Account ac = [SELECT Name,ParentId,Id FROM Account where Id =: ca.Account_Name__c];
List<AccountTeamMember> atmcpd =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE AccountId =: ac.Id AND TeamMemberRole = 'Corporate Partnership Director' ];
List<AccountTeamMember> atmMS =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE AccountId =: ac.Id AND TeamMemberRole = 'IMS' AND Isdeleted = FALSE ];
List<AccountTeamMember> atmMDS =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE AccountId =: ac.Id AND TeamMemberRole = 'Media Services' AND Isdeleted = FALSE ];
List<AccountTeamMember> atmDM =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE AccountId =: ac.Id AND TeamMemberRole = 'Digital Manager' AND Isdeleted = FALSE ];
if(atmcpd!= null || atmcpd.size() > 0 )
{
// User u = [select Id,Name from user where Id=: atm.get(0).UserId];
//System.debug('******Size*******'+atmcpd.size()+'**********'+atmcpd.get(0).UserId);
if(atmcpd.size() > 0)
ca.Corporate_Partnership_Director_1__c = atmcpd.get(0).UserId;
if(atmcpd.size()>1)
ca.Corporate_Partnership_Director_2__c = atmcpd.get(1).UserId;
if(atmcpd.size()>2)
ca.Corporate_Partnership_Director_3__c = atmcpd.get(2).UserId;
if(atmcpd.size()>3)
ca.Corporate_Partnership_Director_4__c = atmcpd.get(3).UserId;
if(atmcpd.size()>4)
ca.Corporate_Partnership_Director_5__c = atmcpd.get(4).UserId;
}
if(atmms != null || atmms.size() > 0)
{
if(atmms.size() > 0)
ca.Marketing_Solutions_1__c = atmms.get(0).UserId;
if(atmms.size() > 1)
ca.Marketing_Solutions_2__c = atmms.get(1).UserId;
if(atmms.size() > 2)
ca.Marketing_Solutions_3__c = atmms.get(2).UserId;
}
if(atmmds != null || atmmds.size() > 0)
{
if(atmmds.size() > 0)
ca.Media_Services_1__c = atmmds.get(0).UserId;
if(atmmds.size() > 1)
ca.Media_Services_2__c = atmmds.get(1).UserId;
if(atmmds.size() > 2)
ca.Media_Services_3__c = atmmds.get(2).UserId;
}
if(atmdm != null || atmdm.size() > 0)
{
if(atmdm.size() > 0)
ca.Digital_Manager_1__c = atmdm.get(0).UserId;
if(atmdm.size() > 1)
ca.Digital_Manager_2__c = atmdm.get(1).UserId;
if(atmdm.size() > 2)
ca.Digital_Manager_3__c = atmdm.get(2).UserId;
if(atmdm.size() > 3)
ca.Digital_Manager_4__c = atmdm.get(3).UserId;
if(atmdm.size() > 4)
ca.Digital_Manager_5__c = atmdm.get(4).UserId;
}
}
update calist;
}
}
Getting Too many SOQL queries. Please help me how to optimize this code.
Hello,
Please put query statements out of For loop and instead of making 4 query statements inside the for loop combine them to one query statement and place them out of for loop, like :
List<AccountTeamMember> atmcpd =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE (AccountId =: ac.Id AND TeamMemberRole = 'Corporate Partnership Director') OR (ac.Id AND TeamMemberRole = 'Media Services' AND Isdeleted = FALSE) OR (ac.Id AND TeamMemberRole = 'IMS' AND Isdeleted = FALSE) OR (ac.Id AND TeamMemberRole = 'Digital Manager' AND Isdeleted = FALSE)];
Following are few examples of how to avoid this error:
Example of poorly written update code:
----------------------------------------------------------------------------------------------------------------------------------
List<Task> taskList = previouslyCreatedTaskList;
for(Task someTask : taskList){
someTask.customField__c = "some value";
update someTask;
}
----------------------------------------------------------------------------------------------------------------------------------
Example of well written update code:
----------------------------------------------------------------------------------------------------------------------------------
List<Task> taskList = previouslyCreatedTaskList;
for(Task someTask : taskList){
someTask.customField__c = "some value";
}
update taskList;
----------------------------------------------------------------------------------------------------------------------------------
Example of poorly written query code:
----------------------------------------------------------------------------------------------------------------------------------
List<Id> CaseIds = previouslyCreatedCaseIdList;
List<Task> taskList;
for(Id someID : CaseIds){
taskList = [Select Id, Subject FROM Task WHERE whatId = :someId];
// presumably take some action with the tasks returned...
}
----------------------------------------------------------------------------------------------------------------------------------
Example of well written query code:
----------------------------------------------------------------------------------------------------------------------------------
List<Id> CaseIds = previouslyCreatedCaseIdList;
List<Task> taskList;
taskList = [Select Id, Subject FROM Task WHERE whatId IN :CaseIds];
// presumably take some action with the tasks returned...
Hope this helps!