Is is safer (limitwise) to call Database.getQueryLocator() with SOQL String than Static SOQL?

Question

In my Apex Batch start() method I am returning a Query Locator to get the full 50 million records and not be limited to 50k.

Is there a difference between calling

...Database.getQueryLocator('SELECT ... FROM');  // Dynamic SOQL

or

...Database.getQueryLocator([SELECT ... FROM]);  // Static SOQL

I am not so sure how this Query Locator thing works but it looks like it is able to query later or in chunks. So my fear was that calling with an already returned result list might be less scalable.

Answer 1

There is a separate limit for Database.getQueryLocator, and that limit is 50,000,000 rows. It does not matter if you use the String parameter or the [query] parameter, you will be subject to 50,000,000 returned rows. The static method is useful when you don't need to alter the fields, objects, sort order, etc, but you want to have full binding capability, while the dynamic method is useful when you want to be able to dynamically assign fields, objects, and so on, at the cost of binding capability.

// Cannot do this using dynamic queries.
Database.getQueryLocator([select id,name from account where name in :accounts.keyset()]);

// Cannot do this using static queries.
Database.getQueryLocator('select '+fields+' from '+objectname+' where '+conditions);

The static method is useful when you'll only ever be querying a specific object with specific fields (which accounts for probably 99% of all use cases), while the dynamic method is useful when you don't know what fields and objects you'll need. For example, I'd imagine that Field Trip (available on the AppExchange) uses this method, because it needs to be able to query any field and any object to determine data population.

Answer 2

One advantage to getting a queryLocator dynamically is that you could potentially pass an updated String to your batch class that would query for a different list of records. For example:

global string queryString;

global Batch_Example(String updatedQueryString)
{
    if (updatedQueryString != null)
        queryString = updatedQueryString;
    else
        queryString = 'select Id from Contact';
}

global start()
{
    return Database.getQueryLocator(queryString);
}

In this case you could choose to target the Batch to only work on a specific subset of Contacts, rather than the entire database:

Database.executeBatch(new Batch_Example('select Id from Contact where CreatedDate >= :system.today().addDays(-1)'));

If you are confident that you will never need to modify the query, I don't think it makes any difference which way you get the query locator.