6

I am looking for ways to speed up a complex Batch job which takes days to run in a specific customer org.

This Batch is snapshoting records of arbitrary objects into denormalized (as a single records contains fields of multiple related objects) "backup" object.

public Database.QueryLocator start(Database.BatchableContext context) {
    String complexSoql = 'SELECT a,b,c,..z (SELECT a,b,c,..z FROM Childs__r...), (SELECT...';
    return Database.getQueryLocator(complexSoql);
}

public void execute(Database.BatchableContext context, List<SObject> scope) {
    List<Snapshot__c> snapshots = new List<Snapshot__c>();

    for(SObject sourceRecord : scope ) {
         snapshots.add( createSnapshot(sourceRecord) );
    }

    insert snapshots;
...

Currently in the start() we query all of the source fields including those of related objects. This can sometimes be dozens up to hundreds of fields. Starting the batch currently can take more than 10 minutes and each Batch job (a single call of execute()) yields a big 15 second delay on the Server describes here in more detail.

enter image description here

We ran into many hard to reproduce or fix errors. Some of them documented here:

Now the idea came up if one could speed up things by dramatically simplifying the start() query. Select only the Id field and get rid of all the sub-selects. Instead we query the full objects per execute().

public Database.QueryLocator start(Database.BatchableContext context) {
    String simpleSoql = 'SELECT Id FROM...";
    return Database.getQueryLocator(simpleSoql);
}

public void execute(Database.BatchableContext context, List<SObject> scope) {
    String soql = 'SELECT many fields, (SELECT many fields FROM...)... WHERE Id IN :scope';
    for(SObject sourceRecord : Database.query(soql) ) {
...

Would this speed things up and why? Are there any other best practices to make Batch classes run faster?

2 Answers 2

5

In the start method, a database cursor is created. You can think of a database cursor as a temporary table that provides consistent results across the life of the cursor. While it is true that this implies staleness, it also implies a proper snapshot-- all of the data as it was at a specific point in time. Moving that query to execute will cause you to have possibly inconsistent data.

For example, let's say that John Doe worked at Contoso, and now works for Acme. Let's also suppose that the query was alphabetical on account name in ascending order. We'll also say, for sake of argument, this change was made to John Doe during the snapshot process (between the start time and the time it got to his company). With the entire query in the start method, the snapshot would show John Doe only once, working at Contoso, which is out of date, but would be captured on the next snapshot. Conversely, live queries would miss John Doe if Acme was queried before he was updated and Contoso was queried afterwards. Conversely, if he was moving from Acme to Contoso, and was moved after Acme and before Contoso, he would appear in the snapshot twice.

That said, moving the query to execute would speed up the initial query, because it takes less time to execute many small queries than it does to create a larger cursor. Just be aware that it will be more likely to miss data or duplicate data in some scenarios. If records can't move, then this is less of a concern, although data will still be skewed.

9
  • Sfdcfox: We also had inconsitencies when we queried everything in start(). That's why we had to lock out all users during the run of the Batch. Are you saying all the data is moved to a a seperate table when using QueryLocator and such a locking is not needed?? Dec 9, 2014 at 8:04
  • 2
    @RobertSösemann I'd be surprised to see inconsistent snapshots while using a cursor. I wonder if an internal developer could shed more light on that subject. As far as I know, the database is supposed to follow ACID (Atomicity, Consistency, Isolation, and Durability). Seeing inconsistent data would violate "C", in which case you might have discovered a bug. Regardless, locking out all users while the process runs may prove beneficial, so long as the lock doesn't interfere with normal business hours.
    – sfdcfox
    Dec 9, 2014 at 8:49
  • 1
    It's entirely possible that the subqueries are mucking things up. Would it be possible to kick off say, one batch per subquery, all launched in parallel? Do you have more than five concurrent batches to worry about?
    – sfdcfox
    Dec 9, 2014 at 8:50
  • 1
    I'm doing to see if we can't find a developer that can answer this. I'd swear it must be like any other database, but maybe we're missing something.
    – sfdcfox
    Dec 9, 2014 at 9:16
  • 1
    @sfdcfox actually I think that the behaviour of Batch Apex changed some time around 2012. Previously a snapshot was created and provided consistent data across the job, but following the change, only IDs were cached. I tested this recently in relation to another answer. Oct 27, 2015 at 8:06
2

Your proposed change would speed up the start() method because it would only be searching for the IDs of a simple set of objects, as I'm sure you know.

It would also improve the accuracy of your snapshot because using your original query in the start() method

SELECT x, y, z, (SELECT a, b, c FROM ...), (SELECT d, e, f FROM ...) ...

The values of x, y, z, a, b, c etc are retrieved in the start() method and played back into the batch. If the batch takes days to run, these values could easily have changed by the time the batch gets round to processing them, but it doesn't know.

If you just select the main ID in the start() method, and then query the full dataset for each batch in the execute() method, your snapshot will be up to date at the time it is written.

7
  • Thanks about pointing out that this design would prevent "stale" data. But I am a bit confused by that. I thought using a QueryLocator would not "freeze" the data in the start() methods but instead just keep something like a database cursor, which will retrieve the data when a specific execute() method is called. Dec 8, 2014 at 13:57
  • 1
    I thought that too, but Andy Fawcett says we were both wrong, and it freezes the data in start() method. This is borne out by an issue that I had with a batch process recently.
    – Dominic
    Dec 8, 2014 at 14:01
  • Indeed I also heard him saying this, but I could not find anything about this in the docs. Dec 8, 2014 at 14:07
  • 1
    I did have an issue with a batch process recently that led me to think he was right. I switched it to get the data in the execute() and the issue was solved
    – Dominic
    Dec 8, 2014 at 14:08
  • But would this also speed up the execute() or slow them down even more? Dec 8, 2014 at 14:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .