Batch Apex : Too many SOQL Queries 201

Question

I have created a batch APEX, below code

global class batchApexJob implements Database.Batchable<sObject> {
        global Database.QueryLocator start(Database.BatchableContext BC) {
        String UsrQuery='';  
        UsrQuery = 'SELECT Id,isActive,NewlastLoginDate__c,Notification_email_Sent__c,Newisactive__c,LastLoginDate,Email,ManagerId,Manager.Name FROM USER WHERE isActive = true AND LastLoginDate !=null';
        return Database.getQueryLocator(UsrQuery);
    }

    global void execute(Database.BatchableContext BC, List<user> usrRecords) {

        List<User> localuseRec = new List<User>();
        List<Id>Ids = new List<Id>();
        boolean isAccFound = false;
        EmailTemplate etId=[Select id from EmailTemplate where name = 'ManagerEmail' limit 1];

        List<Messaging.MassEmailMessage> listofemails  = new List<Messaging.MassEmailMessage>();

        if(usrRecords.size()>0){
            for (User u : usrRecords){
                if(System.now() > u.LastLoginDate.addDays(60)){
                    for (Account a : [Select Id,ownerId from Account where ownerId =:u.Id LIMIT 1]){
                        // for less than 90 days
                        isAccFound = true;
                            system.debug('@@@'+u);
                            Messaging.MassEmailMessage mail = new Messaging.MassEmailMessage ();
                            mail.setTargetObjectIds(ids);
                            mail.setSenderDisplayName('Notification 1');
                            mail.setTemplateId(etId.id);
                            mail.setSaveAsActivity (false);
                            listofemails.add(mail);
                            //  Messaging.SendEmailResult[] r1 = Messaging.sendEmail(new Messaging.MassEmailMessage [] { mail });
                            //  system.debug('Email Result1:-'+r1);
                    }
                }
                localuseRec.add(u);
            }
            Messaging.SendEmailResult[] r4 = Messaging.sendEmail(listofemails);
        }
        Database.SaveResult[] results = Database.update(localuseRec,false);
    }
    global void finish(Database.BatchableContext BC) {

    }
}

In the for loop, I am using Query for Account with LIMIT 1
(Account a : [Select Id,ownerId from Account where ownerId =:u.Id LIMIT 1]){

But still I am getting ERROR

08:53:57.0 (861836685)|SOQL_EXECUTE_BEGIN|[29]|Aggregations:0|SELECT Id, ownerId FROM Account 08:53:57.0 (861859519)|EXCEPTION_THROWN|[29]|System.LimitException: Too many SOQL queries: 201 08:53:57.0 (861947703)|HEAP_ALLOCATE|[29]|Bytes:30 08:53:57.0 (861996900)|FATAL_ERROR|System.LimitException: Too many SOQL queries: 201

As per the governor limits for each transaction i can query till 200 records so I am confused as in why I am getting this error since its LIMIT 1 for Account SOQL https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm

Answer 1

Map is a great way to avoid SOQL query inside for loop. Utilize it!

for (Account a : [Select Id,ownerId from Account where ownerId =:u.Id LIMIT 1]){

This line that you have written is only used to check whether the u.Id is the owner of any account or not, you can do this using a map as well.

Create a map of owner id to account record and store the accounts in that before your loop.

Map<Id, Account> ownerToAccountMap = new Map<Id, Account>();
for(Account a : [Select Id,ownerId from Account where ownerId IN :usrRecords])
{
    if(!ownerToAccountMap.containsKey(a.OwnerId))
        ownerToAccountMap.put(a.OwnerId, a);
}

and then in your loop, check if the map has any records for the loop user variable u.

if(usrRecords.size()>0){
    for (User u : usrRecords){
        if(System.now() > u.LastLoginDate.addDays(60)){
            if(ownerToAccountMap.containsKey(u.Id)){
                isAccFound = true;
                ...
            }
        }
        localuseRec.add(u);
    }
    Messaging.SendEmailResult[] r4 = Messaging.sendEmail(listofemails);
}

Hope this will help!

Answer 2

You have soql query inside for loop. Try to rewrite your code in the following way.

global class BatchApexJob implements Database.Batchable<sObject>, Database.Stateful {
    private Id EmailTemplate etId;

    global Database.QueryLocator start(Database.BatchableContext BC) {
        etId = [Select id from EmailTemplate where name = 'ManagerEmail' limit 1].Id;
        return Database.getQueryLocator([SELECT Id,isActive,NewlastLoginDate__c,Notification_email_Sent__c,Newisactive__c,LastLoginDate,Email,ManagerId,Manager.Name FROM USER WHERE isActive = true AND LastLoginDate !=null]);
    }

    global void execute(Database.BatchableContext BC, List<User> usrRecords) {
        List<User> inactiveUsers = getInactiveMoreThen60DaysUsers(usrRecords);
        Set<Id> inactiveUsersIds = new Map<Id, User>(inactiveUsers).keySet();
        List<Messaging.MassEmailMessage> emails  = new List<Messaging.MassEmailMessage>();
        for (Account a : [Select Id,ownerId from Account where ownerId in :inactiveUsersIds]){
            emails.add(createEmail(a.OwnerId));
        }
        Messaging.SendEmailResult[] result = Messaging.sendEmail(emails);
    }

    private List<User> getInactiveMoreThen60DaysUsers(List<User> users){
        List<User> result = new List<User>();
        for(User usr:users){
           if(System.now() > usr.LastLoginDate.addDays(60)){
               result.add(usr);
           }
        }
        return result;
    }

    private Messaging.MassEmailMessage createEmail(Id targetId){
        Messaging.MassEmailMessage mail = new Messaging.MassEmailMessage ();
        mail.setTargetObjectIds(targetId);
        mail.setSenderDisplayName('Notification 1');
        mail.setTemplateId(etId);
        mail.setSaveAsActivity (false);
        return mail;
    }

    global void finish(Database.BatchableContext BC) {

    }
}

Answer 3

Do query outside the for loop and pass the list in to the for loop.

List<Account> finalList=[Select Id,ownerId from Account where ownerId IN :usrRecords];
//and pass the finalList in to the for loop

for(Account Acc :finalList){
....
}