Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
2  
Is AccountRefId__c a text field or an actual lookup field? – sfdcfox Feb 23 at 18:07
    
text field...... – pmvsdt Feb 23 at 18:08
up vote 9 down vote accepted

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.

share|improve this answer
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 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 at 18:19
1  
Crap, no you would still need to filter != null. – Adrian Larson Feb 23 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 at 18:28
1  
@sfdcfox Or just AccountCount__c = 2. – Adrian Larson Feb 23 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.

share|improve this answer
    
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 – pmvsdt Feb 23 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 – pmvsdt Feb 23 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 at 18:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.