+ Start a Discussion
kittu9kittu9 

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.

Vinita_SFDCVinita_SFDC

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!