System.Exception: Too many SOQL queries: 101 [ Edited ]

[mustafatop]

I am working on some code which sends mail to users who are not logged in the system for a long time. I want to deploy this code to a sf production environment that is including more than 150 users. But I'm getting this error  despite of query returns less than 100 users.  Do you have any idea about this issue?

I am using the following code.

global class LastLoginReminder implements Database.Batchable<sObject>, Schedulable {

    global Database.QueryLocator start(Database.BatchableContext BC) {
      return !Test.isRunningTest() ? Database.getQueryLocator([SELECT Id, LastLoginDate FROM User
           WHERE IsActive=false AND ((LastLoginDate <:Date.today().addDays(-28) AND LastLoginDate >:Date.today().addDays(-29)) OR
           (LastLoginDate <:Date.today().addDays(-22) AND LastLoginDate >:Date.today().addDays(-23)))]) : Database.getQueryLocator([Select Id,LastLoginDate From User Limit 3]);
    }  
 
   global void execute(Database.BatchableContext BC, list<User> users) {
        Messaging.SingleEmailMessage[] mails = new Messaging.SingleEmailMessage[0];
        for(User u : users) {
            Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
            mail.setTargetObjectId(u.id);
            mail.setTemplateId(u.LastLoginDate.date().isSameDay(Date.today().addDays(-23)) ? '00Xa0000001Ifne' : '00Xa0000001IfnZ');
            mail.saveAsActivity = false;
            mails.add(mail);
        }
        Messaging.sendEmail(mails);
    }
    global void finish(Database.BatchableContext BC) {
        system.debug('batch job completed.');
    }
    global void execute(SchedulableContext ctx) {
        LastLoginReminder schedulableJob = new LastLoginReminder();
        Database.executebatch(schedulableJob);
    }
    public static testMethod void myTest() {
        Test.StartTest();
        LastLoginReminder schedulableJob = new LastLoginReminder();
        schedulableJob.execute(null);
        Test.stopTest();
    }
}

[AdrianCC]

Hello!

Code seems to be good. Really weird...

Put system.debugs around the code and check the debug logs to see how many times the soql is executed...

Add the second parameter to executebatch. Try to use query(String) instead of getQueryLocator()(I know it shouldn't make any difference, but I don't have any ideas)...

Have you started the job in the sandbox and it worked? I.e. it did send the mails corectly?

Do you have any other triggers that could fire for this job?

Thanks,

Adrian

[mustafatop]

Hi Adrian,

First, when I started the job at Sandbox, it was sending mails correctly. I am working on only an apex class, no any trigger.

Class is batchable, how can I find number of executed soqls? If you want to know the number of users that returns from the response of query, the result is less than 100. By the way, I thought that soql runs only once.

I've added the second parameter as 99 to executebatch but nothing changed.

How can I use query(String) method inside start method? Its return type is sObject[]. Whereas its return type must be Database.QueryLocator.

Thanks.

[AdrianCC]

I was thinking that maybe the limit is reached not in your class but in some other piece of code that gets called upon execution, most likely a trigger. But you don't have any dmls visible so we should be good...

To see the limits read this: http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_methods_system_limits.htm 

You could add an if statement in the start to return only 100 and see if it still crashes => to see if that's the piece of code that reaches the limit. 

Yes, you are right about query(String). I've confused it with getQueryLocator(String). Your start should look like this:

  global Database.QueryLocator start(Database.BatchableContext bc) {
    String query = 'SELECT Id, LastLoginDate FROM User WHERE IsActive=false AND ((LastLoginDate <:Date.today().addDays(-28) AND LastLoginDate >:Date.today().addDays(-29)) OR (LastLoginDate <:Date.today().addDays(-22) AND LastLoginDate >:Date.today().addDays(-23)))';
    System.debug('Database.getQueryLocator(query) has this value: ' + Database.getQueryLocator(query));
    return Database.getQueryLocator(query);
  }

 You could also try to separate the batch part and schedule part in 2 classes, one for each.

And if your license count permit you could try to create a test case for more users. You should modify your test case to really do smth. If you don't use see all data and you create a test user, you could assert that 1 mail was sent(use Stateful to preserve a counter between executes)...

Yes, the soql should execute only once. That's why I said it was weird :( . 

To see the debug logs you'll need to run the batch by hand from the developer console. Enabling Debug Logs from Administration Setup > Monitoring might also help... Add System.debugs to better follow the execution.

Sorry if my advice seems general. Maybe somebody else has a better idea why it's crashing w that error...

Happy Friday,

Adrian