0

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

  • Try to aviod For loop inside a for loop.that will help you to solve this issue. – user44771 Jun 5 '17 at 7:20
  • If you do not execute the batch with a scope size of 1 then it will process 2000 records per go by default (I think its 2000 but defiantly more than 200). Aside from writing the batch to actually handle batch processing you can execute with cope of 1 like database.executeBatch([BatchIntance],1) – Eric Jun 6 '17 at 5:54
2

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!

  • thanks for the suggestion. I truely got your point. I jsut want to know how that 200 limit is getting exceeded in a standard batch size. Since I am using LIMIT 1 so definitely it should run only once. So, as per the scope it should be 200 times the query will be executed. So how come it exceeds the limit as in batch we can execute 200 SOQL. Thanks again, – Abhik Dey Jun 5 '17 at 15:57
  • my assumtion is wahtever i am doing a SOQL query in query locator (start method) to get the scope , that also adds to the count (my best guess) so it exceeds i beleive – Abhik Dey Jun 5 '17 at 16:01
  • 2
    @AbhikDey You're also querying for the email template above the for loop, so that is more than likely how you're hitting 201. – Robert Watson Jun 6 '17 at 3:26
  • First thing first, LIMIT 1 has nothing to do with the limit of 200 SOQL queries in one batch transaction. From what I see, in your standard batch size, you are firing a SOQL query for each user record, so 200 SOQLs. And then you have one more at the beginning on EmailTemplate, so 201, like @RobertWatson said. – Santanu Halder Jun 6 '17 at 6:24
  • start method gets its own governor limits, separate from batch execution. – Santanu Halder Jun 6 '17 at 6:25
1

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) {

    }
}
  • THanks for your post, I want to execute the Account SOQL before checking the inactivity period of 60 days or so for each user record. Based on the account results the later logic will be executed and notification will be sent. So, i need the SOQL to be checked against each user in batch – Abhik Dey Jun 5 '17 at 8:39
1

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){
....
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.