Exception: QUERY_TIMEOUT Your query request was running for too long

Question

Querying on approx 25 million contacts using the following query in database.getQueryLocator. Used Query Resource Feedback (Link) to get some feedback about query and here are the results.

Query - 1:

SELECT id, accountId, AccountRefId__c FROM Contact where accountId = null

Cardinality: 66 sObject Cardinality: 18 million Relative Cost: 0.000

My actual query is to find contacts with either accountrefid__c or accountid = null. Query -2:

SELECT id, accountId, AccountRefId__c FROM Contact where accountId = null or accountrefId__c = null

Cardinality: 18 million sObject Cardinality: 18 million Relative Cost: 5.381

To reduce the relative cost I made accountrefid__c field as externalId and unique. Also added some filters and limit as well as below: (Query -3)

SELECT id, accountId, AccountRefId__c FROM Contact where (accountId = null or accountrefId__c = null) order by lastmodifieddate desc limit 100

but the relative cost and cardinality staying the same (even though I added limit ) and when I am running this query in my batch class getting query_timeout exception.

Thanks.

Answer 1

Take a look at Working with Very Large SOQL Queries. Querying for field != null will hurt your performance.

• Typically, a custom index isn’t used in these cases.

  • The queried values exceed the system-defined threshold.
  • The filter operator is a negative operator such as NOT EQUAL TO (or !=), NOT CONTAINS, and NOT STARTS WITH.
  • The CONTAINS operator is used in the filter, and the number of rows to be scanned exceeds 333,333. The CONTAINS operator requires a full scan of the index. This threshold is subject to change.
  • You’re comparing with an empty value (Name != '').

I don't have 25M records to test against, but I believe you might have better luck creating a formula along the lines of Is_Missing_Account__c:

AND(ISNULL(AccountId), ISNULL(accountrefId__c))

If the above does not work, you can use an Apex Trigger, a Workflow Rule, or Process Builder to sync an analogous checkbox. You will need to run a back-fill batch, but once you do that, you can just filter on this custom checkbox instead and it should speed things up.

Another option is to populate an Account_Count__c (formula or otherwise) number field with 0, 1, or 2 depending on these parent relationships. Then filter Account_Count__c = 2.

Answer 2

Text field indexes do not index "null" values. In other words, the database can't reduce the cost for a query that filters for a null value in a text field. If most of your table has values, but a few do not, this can easily cause the query timeout exception you're receiving. Odds are, you'll probably need to resort to more drastic measures, such as perhaps manually performing a full table scan:

public class MyBatch implements Database.Batchable<SObject> {
    public Database.QueryLocator start(Database.BatchableContext context) {
        return Database.getQueryLocator([SELECT Id FROM Contact]);
    }
    public void execute(Database.BatchableContext context, Contact[] scope) {
        Contact[] records = [SELECT Id, AccountId, AccountRefId__c FROM Contact 
                             WHERE Id IN :scope AND 
                                   (AccountId = NULL OR AccountRefId__c = NULL)];
        // Now process these records.
    }
    public void finish(Database.BatchableContext context) {
    }
}

This will work for up to 50,000,000 rows, after which point you'd have to also implement batch chaining with large limits, etc.