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.


  • 2
    Is AccountRefId__c a text field or an actual lookup field?
    – sfdcfox
    Feb 23, 2016 at 18:07
  • It is a text field.
    – sf_user
    Feb 23, 2016 at 18:08

2 Answers 2


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.

  • 1
    +1, but I'm pretty sure a formula will have the same problem, though-- it will result in a full table scan.
    – sfdcfox
    Feb 23, 2016 at 18:16
  • 1
    @sfdcfox I figured as much. The root cause, I have identified. The solution, less so. Perhaps creating a solution to manually sync a Boolean field would work? A batch would still be necessary though, but it would use positive filters instead so shouldn't struggle as much.
    – Adrian Larson
    Feb 23, 2016 at 18:19
  • 1
    Crap, no you would still need to filter != null.
    – Adrian Larson
    Feb 23, 2016 at 18:20
  • 1
    I think you could also be creative by using, say, a number field and assign a random negative number if either field is null, or a positive number otherwise. Update this with a workflow field update, run it through a simple update batch. Then, you should be able to query something like WHERE AccountNullCheck__c < 0. This should leverage indexing and give useful performance. Just a thought. Or perhaps a text field that has magic values that represent a null condition...
    – sfdcfox
    Feb 23, 2016 at 18:28
  • 1
    @sfdcfox Or just AccountCount__c = 2.
    – Adrian Larson
    Feb 23, 2016 at 18:29

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.

  • Thanks sfdcfox. I initially thought of this and querying all contacts is working. But have to schedule this batch everyday. If I consider all existing contacts, I am concerned about any extra time it might take
    – sf_user
    Feb 23, 2016 at 18:21
  • I just used "select id from contact" in the query feed back and noticed that the relative cost is still 5.381. I am afraid even if that will throw the query_timeout exception
    – sf_user
    Feb 23, 2016 at 18:29
  • 1
    @pmvsdt I'm pretty sure you can pass over 25,000,000 records in a daily batch. Depending on how long it takes to perform a DML on contact, you could probably get away with using about 10 hours a day. I don't think you could get much better than that, though. But I also commented on ways you might be able trick indexing to work for you on Adrian's answer as well. The problem is nothing to with the "volume" of records so much as the filtering you're trying to do.
    – sfdcfox
    Feb 23, 2016 at 18:32

You must log in to answer this question.

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