8

I have a Batch iterating over a large amount of records (with many fields) and 2 of their related child records. The start() method uses Dynamic SOQL to fetch them via QueryLocator to vary which fields are queried.

global Database.QueryLocator start(Database.batchableContext context) {

    programTypeFacade.setRunningBatch(context.getJobId());

    String soql = Utils.getSelectXFrom('Volume__c');

    String relationSubQueries = ', (SELECT num_Volume_01__c, num_Volume_02__c, num_Volume_03__c, chk_LockVolume_03__c ' +
                                '   FROM VolumeSchedules__r)' +
                                ', (SELECT mdr_Volume__c, lkp_VolumeData__r.txt_Mnemonic_1__c, lkp_VolumeData__r.pkl_Status__c, lkp_VolumeData__r.' + String.join(programTypeFacade.relevantAttributeFields,', lkp_VolumeData__r.') +
                                '   FROM VolumeDataLinks__r)'; 

    soql = soql.replace('FROM', relationSubQueries + ' FROM');
    soql += ' WHERE mdr_ProgramType__c = \'' + programTypeFacade.Id + '\'';

    return Database.getQueryLocator(soql);
}

and this is the Batch Apex log output

SELECT ...FROM Volume__c  
16:57:12.863 (863677615)|SOQL_EXECUTE_END|[44]|Rows:21357 
17:01:28.956 (256956271398)|SYSTEM_METHOD_EXIT|[33]|LIST<UP2GO_2F__Volume__c>.size()   
17:01:28.956 (256956299794)|SYSTEM_METHOD_EXIT|[48]|Database.QueryLocatorIterator.hasNext()    
17:01:28.956 (256956366758)|SYSTEM_METHOD_ENTRY|[33]|LIST<UP2GO_2F__Volume__c>.size()    
17:01:28.956 (256956378599)|SYSTEM_METHOD_EXIT|[33]|LIST<UP2GO_2F__Volume__c>.size()

...(hundreds of equal lines...

17:03:43.785 (391785456348)|SYSTEM_METHOD_EXIT|[38]|LIST<UP2GO_2F__Volume__c>.size()    
17:03:43.785 (391785470977)|SYSTEM_METHOD_ENTRY|[39]|Database.QueryLocator.querymore(Integer)    
17:03:43.973 (391973215599)|EXCEPTION_THROWN|[39]|System.LimitException: Apex CPU time limit exceeded

Is this like a Query Timeout? Can I prevent this and how?

EDIT: This must be a bug a the query result is really small: 32.000 records with max. 200 child records. If I run the same query in the console the result comes back in a second without any problems.

  • This feels like a platform bug, since your clearly not doing much Apex processing by the sounds of it? Can you share some code just to ensure i'm not making any assumptions here. – Andrew Fawcett Jul 17 '14 at 8:16
  • @AndrewFawcett: This definitely is a bug as the query takes a second when executed in the console and the amount of returned records is small. I added more details to the question. Do you think I should open a Case for this? – Robert Sösemann Jul 17 '14 at 9:05
  • @AndrewFawcett: This might be related to CA_Petersons question salesforce.stackexchange.com/questions/1675/… – Robert Sösemann Jul 17 '14 at 9:15
  • Your hundreds of equal lines... comment in the code could suggest a possible issue. If you are comparing SObjects using == then you could hit the CPU limit, e.g. if (objectA == objectB) – BarCotter Jul 17 '14 at 12:07
  • @BarCotter by saying hundreds of equal lines I meant that I skipped log lines in my question that look exatecly like the ones already shown. No use of == or the equals() method here ;-) – Robert Sösemann Jul 17 '14 at 12:18
5

I suggest querying the child records in the execute method instead. This should speed up the start() method significantly. And it should solve the cpu time limit problem as well.

public Database.QueryLocator start(Database.batchableContext context) {
    return Database.getQueryLocator(
        [SELECT Id FROM Foo__c WHERE ... AND ...]
    )
}

public void execute(Database.BatchableContext context, List<Foo__c> rawRecords){
    List<Foo__c> records = [SELECT Field1, Field2, (SELECT FieldA FROM Bar__r) FROM Foo__c WHERE Id IN :rawRecords];

    //do something
}

The second query at the execute() method should also perform much better, as you are querying only a subset of records corresponding to the batch size.

My example is not using dynamic SOQL, but this would work as well.

  • I agree to a certain poing that this might speed up the query but as I noted in my question we do not have any speed problems. Executing the query in the dev console takes half a second and we have not really Large Data Volumes (TM Salesforce.com) here. Additionally there is a downside to querying child records in seperate soqls You cannot reference a records childs by using parent.Childs__r in code but have to fumble around with maps. If you don't need that clean code should not use it ;-) – Robert Sösemann Jul 17 '14 at 12:27
  • 1
    Have you tried, if this approach would fix your problem? If you inspect the execute() method i posted, you see that this is a parent to child query. So there is no map fumbling required. – Alexander Johannes Jul 17 '14 at 12:32
  • 1
    Or, don't worry about it. The Summer '14 release includes a fix that increases the start time by 10x. Your code should run fine. P.S. It kind of is a "bug", but related to how Salesforce post-processes the return value from the start method. I suspect that this answer would fix the problem, but since an impending fix is coming this weekend, I wouldn't worry about fixing it. – sfdcfox Jul 17 '14 at 12:56
  • 2
    I also generally do the querying in the execute and query only the Id's, since the records passed back in the execute are stale (pre-cached at the time of the start). If your job runs for a long time you run the risk updating the database with stale data. – Andrew Fawcett Jul 17 '14 at 14:20
  • As the answer of @sfdcfox didn't work I will try you solution next. – Robert Sösemann Jul 21 '14 at 15:18
8

Behind the scenes, when start() returns, it is handed back to a process that begins the process of breaking the items into the appropriate scope size. The process roughly looks like:

List<List<Object>> objects = new List<List<Object>>();
Object[] temp = new Object[0];
Database.QueryLocator locator = batch.start(context);
Iterator<Object> iterator = locator.iterator();
while(iterator.hasNext()) {
    temp.add(iterator.next());
    if(temp.size()==scopeSize) {
        objects.add(temp);
        temp = new Object[0];
    }
}
// other housekeeping

All you really need to know is that there's code running outside your code that runs with the same rules as normal Apex Code-- it has a CPU limit of 60,000 ms (one minute of code time), after which the process will die.

For simple queries, this is usually more than enough time, but in some cases, your batch process will die before it gets a chance to run (I'd imagine with more complicated queries/larger queries/etc).

There is good news, however. Summer '14 includes an update that will change the limit from 60,000 ms to 600,000 ms (10 minutes), which should make your code fully functional after this weekend's update. Since a fix is forthcoming, I wouldn't try to fix the problem yourself.

  • Very cool...but what still strikes me is that running this query from the dev console only took milliseconds.... One minute just for Housekeeping ? ;-) – Robert Sösemann Jul 17 '14 at 14:19
  • 2
    I'm kind of surprised myself, as I've easily handled 50m records in a batch. I suppose the problem occurs with child relationships, and I'd bet that is the reason for the increase. Custom iterators also suffer the same time limit problems. No single iterator can actually process 50m data elements, as far as I'm aware. – sfdcfox Jul 17 '14 at 14:40
  • The org was upgraded to Summer '14 this weekend but the CPU limit exception happens as before. So I had to retake the "Answer" from you reply. Any ideas how to else solve it? I guess putting the query into the execute() is the smartest thing to do?! – Robert Sösemann Jul 21 '14 at 15:18
  • I'm wondering if the documentation is wrong or just unclear? I wouldn't imagine a timeout with ten whole minutes. Can you check the logs? – sfdcfox Jul 21 '14 at 15:59
  • All of the batches start() methods fail after 5 to 10 minutes with a "Apex CPU time limit " error. – Robert Sösemann Jul 21 '14 at 18:44

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.