[SalesForce] Batch Apex : Too many SOQL Queries 201

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

Best Answer

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!

Related Topic