Salesforce: Getting the “Non selective query” error on a selective query

Question

I'm running a very simple SOQL query which I've renamed objects and included here:

List <ObjectA__c> allRecords= [
select name, Id,Contact__c,...
from ObjectA__c
where Contact__c IN 
(SELECT Contact__c FROM ObjectB__c WHERE id IN :idList) 
];

ObjectB has only 300 records, objectA has over 200k, so it's clearly that object that's causing the problem.

id is indexed, obviously, and objectB's contact field is a master-detail so that's indexed too, and cannot include a null.

I'm open to any suggestions that might help. Troubleshooting has only produced this error in imports (where the idList is built off trigger.new) and running the code manually in anon apex runs fine without problems.

I'm running out of "things to try" so would welcome advice.

So just thought I'd share, I just learned that the "can not query an object with over 100000 records with a non-selective query" only happens in triggers, never anon-apex or other methods of called classes (unless they're called from a trigger). I put my code into a trigger and was able to cause it to fail, so now I'm able to troubleshoot and work on a solution. — ScriptMonkey, May 14 '14 at 14:30

score 2 · Answer 1 · answered May 15 '14 at 00:29

In addition to eyescream's suggestions, you could try the new Query Resource Feedback Parameter pilot. This will show you how the query will be executed by Salesforce without actually running the query.

The linked article has a Visualforce page and associated Apex Controller that you can use to form these REST queries. Or you can get the same information using the FuseIT SFDC Explorer (Disclose, I currently work for FuseIT. This is a free tool for devs).

SOQL Query Execution Plan

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

I love people who downvote questions without commenting what they think could be improved...

Some general reading material:

Generally speaking - you should consider the fact that optimizing this query now might backfire on you in the long run. If a "right" combination of ObjectB's is used - you'll hit the limit of 50K queried rows and your transaction will roll back. Not to mention the fact that user than manually insert a record with "popular" Contact will wait forever for simple save to finish... Couple ideas:

cut into chunks and offload to @future call
or submit a batch job that can handle such workload easier
or reduce the "batchSize" of your import

In your case - either I'm not reading the question right or you've overly complicated it.

(SELECT Contact__c FROM ObjectB__c WHERE id IN :idList)

the idList is built off trigger.new

plus I assume it happens in trigger somethingSomething on ObjectB__c(after insert)

You already have all your ObjectB__c records in the trigger.new. There's no need to query for them & their contacts.

So if you'll collect all Contact Ids...

Set<Id> contactIds = new Set<Id>();
for(ObjectB__c b : trigger.new){
    contactIds.add(b.Contact__c);
}
contactIds.remove(null);
System.debug(contactIds);

... you'll be able to write a simplified query:

SELECT Id, Name, Contact__c
FROM ObjectA__c
WHERE Contact__c IN :contactIds

If that one will still fail on you and you can't filter them more & can't reduce the batch size - yep, asynchronous processing time (@future or batch job).

Salesforce: Getting the “Non selective query” error on a selective query

2 Answers2