Advice to change class to avoid too many soql queries

Question

I have written my first Batch class below, unfortunately I am receiving the error message apex too many soql queries 101. First of all I have read over the best practice but got a bit lost...advice is appreciated:

global class BatchCreateServiceAppointments implements Schedulable {

    global void execute(SchedulableContext ctx) {
        createNextSetofSiteInspections();
    }

    public void createNextSiteInspections(){
        Date todaysDate =  system.today();
        Date todayPlus7 = todaysDate.addDays(7);
        String gsRecordTypeID = [SELECT id FROM RecordType WHERE name = 'GS' and sobjecttype ='WorkOrder'].Id;
        List<PA__c> palist = [SELECT Id, address_line_1__c, address_line_2__c, address_line_3__c, address_line_4__c, postalcode__c, site__c FROM PA__c];
        for(PA__c pa: palist) {
            Workorder wo = new WorkOrder();
            wo.RecordTypeId = gsRecordTypeID;
            wo.Work_Order_Type__c = 'Site Inspection';
            wo.Work_Order_Category__c = 'PA';
            wo.Status = 'Scheduled';
            wo.Description = 'Please complete a Site Inspection for this PA:' + site__c;
            wo.PA__c = pa.id;
            wo.Location_Description__c = 'The location of the PA in: ' + pa.site__c;
            wo.Required_completion_date__c = todayPlus7;
            insert wo;

            ServiceAppointment sa = new ServiceAppointment();
            sa.ParentRecordId = wo.id;
            sa.Street = wo.PA__r.address_line_1__c;
            sa.City = wo.PA__r.address_line_2__c;
            sa.State = wo.PA__r.address_line_3__c;
            sa.Country = wo.PA__r.address_line_4__c;   
            sa.PostalCode = wo.PA__r.postalcode__c;
            sa.Duration = 20;
            sa.DurationType = 'Minutes';
            sa.FSL__Auto_Schedule__c = true;
            sa.Description = 'Site Inspection required at the site.';
            sa.Subject = 'Site Inspection at PA';
            insert sa;

        }
    }
}

I did change some of the above to post here but as you can see, the class was designed to look up all of the records in the palist and for each one then create a work order and service appointment.

Unfortunately as there is currently 56 records in the palist the job fails as the too many soql queries error appears. I have tested a much more specific SELECT in the list and can provide the code does work...

I did try adding the new workorder's and serviceappointment's to lists and then inserting the list outside of the for loop but I wasn't sure how to related all of the service appointments to the right work orders.

I'd appreciate any advice, I know the "too many soql queries" pops up all over the place but as yet I can't see where I have gone wrong.

Thanks,

P.S I am aware there are issues to resolve (the service appointments aren't appearing in the dispatcher console properly as I am probably inserting the wrong values or missing crucial fields when creating the record) but one step at a time :D

Answer 1

You need to take the insert out of the loop.

something like this

ServiceAppointment[] saTBI = New ServiceAppointment[]{};
Workorder[] woTBI = New Workorder[]{};

    for(....){

       ......
       wo.Required_completion_date__c = todayPlus7;
       woTBI.add(wo);    
    }

    insert woTBI;

for(Workorder wo : woTBI){

  ServiceAppointment sa = new ServiceAppointment();
  sa.ParentRecordId = wo.id; 

  .......

  saTBI.add(sa);

}

insert saTBI;

Answer 2

I see some problems with your code. Consider below observations.

1. Your DMLs are not bulkified. You are doing dml inside loop.

2. There could be trigger on WorkOrder and in that trigger there could be number of SOQL's made, so there fore the you must be hitting the SOQL limit of 101.

Please modify your code to make sure that you are not doing DMLs in loop.