Apex Code Development You need to sign in to do that
Don't have an account?
Intermittent problem with complex SOQL and non-selective query error
I'm facing an issue in a package I've released that has an intermittant problem. There are orgs with 600k contacts and this code works fine, but there is an org with 200k records and it fails with the non-selective query error.
here's the pseudocode:
[SELECT
(SELECT id from objectA),
(SELECT id from objectB)
FROM contact WHERE id IN
(SELECT Contact__c FROM objectC WHERE id IN :listOfObjIDs)]
listOfObjIDs is a list of strings that has IDs in objectC built from an after insert trigger on objectC
objectC is a child object of Contact with a field Contact__c containing its contact
Objects A and B are also children of Contact
ID in indexed, and listOfObjIDs cannot contain any null values unless you can have required fields as null in a trigger.new reference (the parent's value can't be null on a child record).
I'm looking for any advice or suggestions anyone might have, I'm usually pretty solid in SOQL but I can't understand how this is an issue in some orgs and not others.
Additional note: in the org having the problem, the only object in the entire org over 100k records is contact, the rest have less than a few thousand, so it needs to be the contact-based part of the query.
here's the pseudocode:
[SELECT
(SELECT id from objectA),
(SELECT id from objectB)
FROM contact WHERE id IN
(SELECT Contact__c FROM objectC WHERE id IN :listOfObjIDs)]
listOfObjIDs is a list of strings that has IDs in objectC built from an after insert trigger on objectC
objectC is a child object of Contact with a field Contact__c containing its contact
Objects A and B are also children of Contact
ID in indexed, and listOfObjIDs cannot contain any null values unless you can have required fields as null in a trigger.new reference (the parent's value can't be null on a child record).
I'm looking for any advice or suggestions anyone might have, I'm usually pretty solid in SOQL but I can't understand how this is an issue in some orgs and not others.
Additional note: in the org having the problem, the only object in the entire org over 100k records is contact, the rest have less than a few thousand, so it needs to be the contact-based part of the query.
1) [SELECT Contact__c FROM objectC WHERE Id IN :listOfObjIds] //How many unique contact Ids are brought back? Ex, put them all in a Set and get the count
2) [SELECT Id FROM contact WHERE id IN (SELECT Contact__c FROM objectC WHERE id IN :listOfObjIDs)]
3)[SELECT
(SELECT id from objectB)
FROM contact WHERE id IN
(SELECT Contact__c FROM objectC WHERE id IN :listOfObjIDs)]
4)[SELECT
(SELECT id from objectA)
FROM contact WHERE id IN
(SELECT Contact__c FROM objectC WHERE id IN :listOfObjIDs)]
I'm certain it's contact because there are accounts and contacts, nothing else on this org. (It's a new setup).
I can't recreate the problem in developer console on their org, and I can't cause this problem in my dev org because I don't believe the limits allowe me to get over 100k records.
In addition, as I said before, there is another org using this packaged product that has over 600k contacts and it's fine.
I'm trying to find a way to not have to use iterative packaging, installing, testing just to see what the cause is, but I know object A and B have 0 records in them, and C has 1.