Too many SOQL queries - wanting to store in a map and check

Question

I need to maintain a record for each month up to 10 years away, so 120 records ahead. This might be extended in the future!

The code

global with sharing class AccountingPeriodCreationService  {

 public static List <AcctSeed__Accounting_Period__c> accountingPeriod;
 public static Date currentDate;
 public static Date firstDayOfCurrentMonth;
 public static Date lastDayOfCurrentMonth;

 public void createdAPLogic(){


    for(Integer i = 0; i < 120; i++){
    currentDate = Date.today();
    firstDayOfCurrentMonth = currentDate.addMonths(i).toStartofMonth();
    lastDayOfCurrentMonth = currentDate.addMonths(i + 1).toStartofMonth().addDays(-1);

        List<AcctSeed__Accounting_Period__c> checkAccountingPeriodAlreadyExist = [SELECT Id, AcctSeed__Start_Date__c, AcctSeed__End_Date__c FROM AcctSeed__Accounting_Period__c where AcctSeed__Start_Date__c = :firstDayOfCurrentMonth];

        if(checkAccountingPeriodAlreadyExist.isEmpty()){
            accountingPeriod.add(
                new AcctSeed__Accounting_Period__c(
                Name = 'Name',
                AcctSeed__Status__c = 'Status',
                AcctSeed__Start_Date__c = firstDayOfCurrentMonth,
                AcctSeed__End_Date__c = lastDayOfCurrentMonth
                )
            );
        }
    }

        System.debug( 'accountingPeriod: ' + accountingPeriod.size());
        List<Database.SaveResult> accountingPeriod = Database.insert(accountingPeriod, false);


 } 
}

The logic

For each loop iteration, I'm getting the first day and the last day of the month. Storing it in a variable. Then a query to see if a record with the same first date exists (there should only ever be 1 instance). If it doesn't, add the record to a list.

Then I insert the list at the end.

The problem

Is essentially that I'm going to hit limits and I know there are better ways to code this. If I'm correct, I'll have to store all current records into a map, then loop over that map X amount of times to check if it already exists?

Could I get some assistance on how to most efficiently do this please?

Any insight is always appreciated! Thank you

Answer 1

Is essentially that I'm going to hit limits and I know there are better ways to code this. If I'm correct, I'll have to store all current records into a map, then loop over that map X amount of times to check if it already exists?

This is a pretty good summary. Looking at your code, I think what you need to change is this.

for(Integer i = 0; i < 120; i++){
    currentDate = Date.today();
    firstDayOfCurrentMonth = currentDate.addMonths(i).toStartofMonth();
    lastDayOfCurrentMonth = currentDate.addMonths(i + 1).toStartofMonth().addDays(-1);

Instead of going straight to the generation of the Accounting Periods, let's generate a Set representing the key data points that define an already-existing Accounting Period. If that is the start date, for example, build a Set<Date>, and populate it with all the values you generate for firstDayOfCurrentMonth.

    List<AcctSeed__Accounting_Period__c> checkAccountingPeriodAlreadyExist = [SELECT Id, AcctSeed__Start_Date__c, AcctSeed__End_Date__c FROM AcctSeed__Accounting_Period__c where AcctSeed__Start_Date__c = :firstDayOfCurrentMonth];

This query is the key limits problem. It needs to be run exactly once, outside the for loop. If you generate a Set<Date> firstDaysOfMonths as mentioned above, you could change the query to

    List<AcctSeed__Accounting_Period__c> checkAccountingPeriodAlreadyExist = [SELECT Id, AcctSeed__Start_Date__c, AcctSeed__End_Date__c FROM AcctSeed__Accounting_Period__c where AcctSeed__Start_Date__c IN :firstDaysOfMonths];

Now we could, for fast O(1) access to determine if a given Accounting Period exists, convert this List into a Map<Date, AcctSeed__Accounting_Period__c> apMap, where the keys are the start dates, and then look at that Map while we iterate over the numbers 1 to 120 again to determine which records need to be created.

I actually think there's a faster way, though. Iterate over this list, and remove from the Set<Date> all those records you already found:

for (AcctSeed__Accounting_Period__c ap : checkAccountingPeriodAlreadyExist) {
    firstDaysOfMonths.remove(ap.AcctSeed__Start_Date__c);
}

Then, lastly, you can iterate over the remaining Start Dates that you generated in the first loop, build an Accounting Period for each one, and finally insert a single list of all the generated Accounting Periods.

If my thesis that the Start Date is unique was false, you can still follow something like this process; you'd just make changes to use a different field or combination of fields as your unique key, and the logic might be slightly more complex.