+ Start a Discussion
HelloSanHelloSan 

can any one bulkify this batch class,i have encountered with this error System.LimitException: Too many SOQL queries: 201,below is the batch class


global class BatchService implements Database.Batchable<SObject>, Database.Stateful{
    global String query;
    
    global database.querylocator start(Database.BatchableContext BC){ 
                query = 'select id,Accountid,RecordtypeId,Service_Type__c,Total_Amount__c,Initial_Amount__c  from Opportunity';
       return Database.getQueryLocator(query); 
    }
    global void execute(Database.BatchableContext BC, List<Opportunity> Opplist){  
    
       string str = null;
Set<Id> accountids = new Set<Id>();
Map<Id,String> mapacctname = new Map<id,String>();
Map<Id,String> mpstype = new Map<id,String>();
             for(opportunity opp:Opplist)
             {
                 mpstype.put(opp.id,opp.Service_Type__c);
             }

 for(opportunity opp1:Opplist)
{
    
if(opp1!=null)
{
if(opp1.accountId != null)
{
accountids.add(oppty.accountid);
}
List<Account> accounts = [Select id,name from Account where id in:accountids]; 
for(Account acc : accounts)
{
mapacctname.put(acc.id,acc.name);
}
RecordType r=[select id,name from RecordType Where id =:opp1.recordTypeId];
str = mapacctname.get(opp1.AccountId)+' '+mpstype.get(opp1.id)+' '+r.name;
list<Service__c> slist=[SELECT id,name,Accountname__c,Service_A1_Total_Amount__c,Service_A2_Total_Amount__c,Service_A3_Total_Amount__c,Service_A4_Total_Amount__c,Service_Type_Amount__c,Initial_A1_Amount__c,Initial_A2_Amount__c,Initial_A3_Amount__c,Initial_A4_Amount__c,Service_Type_Amount__c,Initial_Type_Amount__c,Service_Type__c from Service__c where name =:str];
   System.debug('before checking the slist  records');
    if(slist.size()==0)
    {
Service__c s=new Service__c();
s.name = mapacctname.get(opp1.AccountId)+' '+mpstype.get(opp1.id)+' '+r.name;
s.Accountname__c = opp1.AccountId;
s.Record_Type__c = r.name;
s.Service_Type__c = opp1.Service_Type__c;
        
if(opp1.Service_Type__c == 'A1')
{
s.Service_A1_Total_Amount__c = opp1.Total_Amount__c;
s.Initial_A1_Amount__c  = opp1.Initial_Amount__c; 
s.Service_Type_Amount__c = opp1.Total_Amount__c; 
s.Initial_Type_Amount__c = opp1.Initial_Amount__c;    
}
else if(opp1.Service_Type__c == 'A2')
{
s.Service_A2_Total_Amount__c = opp1.Total_Amount__c;
s.Initial_A2_Amount__c  = opp1.Initial_Amount__c; 
s.Service_Type_Amount__c = opp1.Total_Amount__c; 
s.Initial_Type_Amount__c = opp1.Initial_Amount__c;    
}
else if(opp1.Service_Type__c == 'A3')
{
s.Service_A3_Total_Amount__c = opp1.Total_Amount__c;
s.Initial_A3_Amount__c  = opp1.Initial_Amount__c; 
s.Service_Type_Amount__c = opp1.Total_Amount__c; 
s.Initial_Type_Amount__c = opp1.Initial_Amount__c;    

else if(opp1.Service_Type__c == 'A4')
{
s.Service_A4_Total_Amount__c = opp1.Total_Amount__c;
s.Initial_A4_Amount__c  = opp1.Initial_Amount__c; 
s.Service_Type_Amount__c = opp1.Total_Amount__c; 
s.Initial_Type_Amount__c = opp1.Initial_Amount__c;    
}
insert s;
}
else
{
list<Service__c> sold=[SELECT id,name,Accountname__c,Service_A1_Total_Amount__c,Service_A2_Total_Amount__c,Service_A3_Total_Amount__c,Service_A4_Total_Amount__c,Service_Type_Amount__c,Initial_A1_Amount__c,Initial_A2_Amount__c,Initial_A3_Amount__c,Initial_A4_Amount__c,Service_Type_Amount__c,Initial_Type_Amount__c,Service_Type__c from Service__c where name =:str];
       for(Service__c s1:sold ) 
  {
   if(opp1.Service_Type__c == 'A1')
{
s1.Service_A1_Total_Amount__c = opp1.Total_Amount__c;
s1.Initial_A1_Amount__c  = opp1.Initial_Amount__c;   
}
else if(opp1.Service_Type__c == 'A2')
{
s1.Service_A2_Total_Amount__c = opp1.Total_Amount__c;
s1.Initial_A2_Amount__c  = opp1.Initial_Amount__c;   
}
else if(opp1.Service_Type__c == 'A3')
{
s1.Service_A3_Total_Amount__c = opp1.Total_Amount__c;
s1.Initial_A3_Amount__c  = opp1.Initial_Amount__c;   

else if(opp1.Service_Type__c == 'A4')
{
s1.Service_A4_Total_Amount__c = opp1.Total_Amount__c;
s1.Initial_A4_Amount__c  = opp1.Initial_Amount__c;   
}
      s1.Service_Type_Amount__c =  s1.Service_A1_Total_Amount__c+s1.Service_A2_Total_Amount__c+s1.Service_A3_Total_Amount__c+s1.Service_A4_Total_Amount__c;
      s1.Initial_Type_Amount__c = s1.Initial_A1_Amount__c+s1.Initial_A2_Amount__c+s1.Initial_A3_Amount__c+s1.Initial_A4_Amount__c;        
 update s1;      
  } 
   }
    
}    
}
}
    global void finish(Database.BatchableContext BC){
        
           AsyncApexJob a = [SELECT Id, Status, NumberOfErrors, JobItemsProcessed,
      TotalJobItems, CreatedBy.Email
      FROM AsyncApexJob WHERE Id =
      :BC.getJobId()];
   // Send an email to the Apex job's submitter notifying of job completion.
   Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
   String[] toAddresses = new String[] {a.CreatedBy.Email};
   mail.setToAddresses(toAddresses);
   mail.setSubject('Service Batch Job' + a.Status);
   mail.setPlainTextBody
   ('The batch Apex job processed ' + a.TotalJobItems +
   ' batches with '+ a.NumberOfErrors + ' failures.');
   Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
    }
}
SivaGSivaG
Hi,

Please check the modified code. The lines 72 thru 101 doesn't make sense to me as the list will be empty.

global class BatchService implements Database.Batchable<SObject>, Database.Stateful{
    global String query;
    
    global database.querylocator start(Database.BatchableContext BC){ 
                query = 'select id,Accountid,RecordtypeId,Service_Type__c,Total_Amount__c,Initial_Amount__c  from Opportunity';
       return Database.getQueryLocator(query); 
    }
    global void execute(Database.BatchableContext BC, List<Opportunity> Opplist){  
    
       string str = null;
Set<Id> accountids = new Set<Id>();
Map<Id,String> mapacctname = new Map<id,String>();
Map<Id,String> mpstype = new Map<id,String>();
Map<Id,String> mpsrectype = new Map<id,String>();
List<String> strlist = new List<String>();
List<Service__c> sInslist = new List<Service__c>();
             for(opportunity opp:[Select id,Service_Type__c,AccountId,Account.name,RecordtypeId,RecordType.Name from Opportunity where Id in:Opplist])
             {
                 mpstype.put(opp.id,opp.Service_Type__c);
                 accountids.add(opp.accountid);
                 mapacctname.put(opp.AccountId,opp.Account.name);
                 mpsrectype.put(opp.RecordtypeId,opp.RecordType.name);
             }
             for(opportunity op : Opplist){
                 String str mapacctname.get(op.AccountId)+' '+mpstype.get(op.id)+' '+ mpsrectype.get(op.RecordtypeId);
                 strlist.add(str);
             }
List<Service__c> slist=[SELECT id,name,Accountname__c,Service_A1_Total_Amount__c,Service_A2_Total_Amount__c,Service_A3_Total_Amount__c,Service_A4_Total_Amount__c,Service_Type_Amount__c,Initial_A1_Amount__c,Initial_A2_Amount__c,Initial_A3_Amount__c,Initial_A4_Amount__c,Service_Type_Amount__c,Initial_Type_Amount__c,Service_Type__c from Service__c where name in :strlist];
             

for(opportunity opp1:Opplist)
{
    
    if(slist.size()==0)
    {
Service__c s=new Service__c();
s.name = mapacctname.get(opp1.AccountId)+' '+mpstype.get(opp1.id)+' '+mpsrectype.get(opp1.RecordtypeId);
s.Accountname__c = opp1.AccountId;
s.Record_Type__c = mpsrectype.get(opp1.RecordtypeId);
s.Service_Type__c = opp1.Service_Type__c;
        
if(opp1.Service_Type__c == 'A1')
{
s.Service_A1_Total_Amount__c = opp1.Total_Amount__c;
s.Initial_A1_Amount__c  = opp1.Initial_Amount__c; 
s.Service_Type_Amount__c = opp1.Total_Amount__c; 
s.Initial_Type_Amount__c = opp1.Initial_Amount__c;    
}
else if(opp1.Service_Type__c == 'A2')
{
s.Service_A2_Total_Amount__c = opp1.Total_Amount__c;
s.Initial_A2_Amount__c  = opp1.Initial_Amount__c; 
s.Service_Type_Amount__c = opp1.Total_Amount__c; 
s.Initial_Type_Amount__c = opp1.Initial_Amount__c;    
}
else if(opp1.Service_Type__c == 'A3')
{
s.Service_A3_Total_Amount__c = opp1.Total_Amount__c;
s.Initial_A3_Amount__c  = opp1.Initial_Amount__c; 
s.Service_Type_Amount__c = opp1.Total_Amount__c; 
s.Initial_Type_Amount__c = opp1.Initial_Amount__c;    

else if(opp1.Service_Type__c == 'A4')
{
s.Service_A4_Total_Amount__c = opp1.Total_Amount__c;
s.Initial_A4_Amount__c  = opp1.Initial_Amount__c; 
s.Service_Type_Amount__c = opp1.Total_Amount__c; 
s.Initial_Type_Amount__c = opp1.Initial_Amount__c;    
}
sInslist.add(s);
}
else
{
list<Service__c> sold=[SELECT id,name,Accountname__c,Service_A1_Total_Amount__c,Service_A2_Total_Amount__c,Service_A3_Total_Amount__c,Service_A4_Total_Amount__c,Service_Type_Amount__c,Initial_A1_Amount__c,Initial_A2_Amount__c,Initial_A3_Amount__c,Initial_A4_Amount__c,Service_Type_Amount__c,Initial_Type_Amount__c,Service_Type__c from Service__c where name =:str];
       for(Service__c s1:sold ) 
  {
   if(opp1.Service_Type__c == 'A1')
{
s1.Service_A1_Total_Amount__c = opp1.Total_Amount__c;
s1.Initial_A1_Amount__c  = opp1.Initial_Amount__c;   
}
else if(opp1.Service_Type__c == 'A2')
{
s1.Service_A2_Total_Amount__c = opp1.Total_Amount__c;
s1.Initial_A2_Amount__c  = opp1.Initial_Amount__c;   
}
else if(opp1.Service_Type__c == 'A3')
{
s1.Service_A3_Total_Amount__c = opp1.Total_Amount__c;
s1.Initial_A3_Amount__c  = opp1.Initial_Amount__c;   

else if(opp1.Service_Type__c == 'A4')
{
s1.Service_A4_Total_Amount__c = opp1.Total_Amount__c;
s1.Initial_A4_Amount__c  = opp1.Initial_Amount__c;   
}
      s1.Service_Type_Amount__c =  s1.Service_A1_Total_Amount__c+s1.Service_A2_Total_Amount__c+s1.Service_A3_Total_Amount__c+s1.Service_A4_Total_Amount__c;
      s1.Initial_Type_Amount__c = s1.Initial_A1_Amount__c+s1.Initial_A2_Amount__c+s1.Initial_A3_Amount__c+s1.Initial_A4_Amount__c;        
 update s1;      
  } 
   }
    
}
 try{
   insert sInslist;
 }
 Catch(DMLException de){
    System.debug('Exception occured' + de);
 }
    
}

    global void finish(Database.BatchableContext BC){
        
           AsyncApexJob a = [SELECT Id, Status, NumberOfErrors, JobItemsProcessed,
      TotalJobItems, CreatedBy.Email
      FROM AsyncApexJob WHERE Id =
      :BC.getJobId()];
   // Send an email to the Apex job's submitter notifying of job completion.
   Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
   String[] toAddresses = new String[] {a.CreatedBy.Email};
   mail.setToAddresses(toAddresses);
   mail.setSubject('Service Batch Job' + a.Status);
   mail.setPlainTextBody
   ('The batch Apex job processed ' + a.TotalJobItems +
   ' batches with '+ a.NumberOfErrors + ' failures.');
   Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
    }
}
HelloSanHelloSan
Thanks Kumar i was able to resolve the issue with your code and could help me in writing the test class for above batch class.