3

I recently deployed a trigger to production. Inside it was the following query:

SELECT Id,
    (SELECT Business_Unit__c, Department__c, Role__c FROM Account_Roles__r WHERE Role__c IN :roles AND Business_Unit__c IN :businessUnits ORDER BY CreatedDate DESC NULLS FIRST )
FROM Account
WHERE Id IN :accIds
    AND Id IN (SELECT Account__c FROM SKY_Account_Role__c)
ORDER BY Name ASC NULLS FIRST 

Unfortunately the trigger started to fail on production with QueryException: Non-selective query error. I am having hard time debugging it since the issue occurs neither on sandbox environment nor in Execute Anonymous in production.

So my question is: which part may be causing that Exception? After some investigation and discussion I'm leaning towards adding filter on semi-join:

    SELECT Id,
    (SELECT Business_Unit__c, Department__c, Role__c FROM Account_Roles__r WHERE Role__c IN :roles AND Business_Unit__c IN :businessUnits ORDER BY CreatedDate DESC NULLS FIRST )
FROM Account
WHERE Id IN :accIds
    AND Id IN (SELECT Account__c FROM SKY_Account_Role__c WHERE Id IN :accIds)
ORDER BY Name ASC NULLS FIRST
  • 1
    If you haven't already, take a look to see if the query plan tool help.salesforce.com/articleView?id=000199003&type=1 offers anything helpful in your sandbox. – Keith C May 20 at 7:55
  • Unfortunately it's completely useless in this scenario, it just says: 'This query plan doesn't have any notes.' – Bartosz Śliwiński May 20 at 7:57
  • 3
    Pity. For what it is worth, your suggested solution would be the first thing I would try. – Keith C May 20 at 8:06
3

I think your analysis is correct. How many records are there in SKY_Account_Role__c? This query will be problematic if there are more than 200,000 SKY_Account_Role__c records:

SELECT Account__c FROM SKY_Account_Role__c

Have you tried your own suggestion of filtering that sub-query yet? You probably want it like this (rather than the one in your question):

SELECT Account__c FROM SKY_Account_Role__c WHERE Account__c IN :accIds
  • Yes, the count is over 200,000. I wasn't 100% sure if semi-joins can also cause this exception since they aren't calculated into limit of SOQL rows. I see that a lot of people agree with that solution so I will push that to production and check what happens. Thanks! – Bartosz Śliwiński May 20 at 8:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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