0

I am getting the following Error. works fine in DEV org but throwing error in UAT. Please suggest me as I was unable to move forward.

The Actual Error is

The first validation error encountered was "Apex trigger OpportunityObject caused an unexpected exception, contact your administrator: OpportunityObject: execution of BeforeUpdate caused by: System.QueryException: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times): Class.OpportunityValidator.ValidateOCR_OnOppStageChange: line 14, column 1".

public class OpportunityValidator {
     public static void ValidateOCR_OnOppStageChange(List < Opportunity > newOppsForUpdate, Map < Id, Opportunity > mapOldOpps) {

      if (Validator_cls.isOCRfirstRun()) {
       Map < String, OCRValidationRequiredRecordTypes__c > mapOCRRequiredRecordTypes = OCRValidationRequiredRecordTypes__c.getAll();
       Map < String, OCRValidationRequiredStages__c > mapOCRRequiredStages = OCRValidationRequiredStages__c.getAll();
       //Map < Id, String > mapRecordtypeIdName = new Map< Id, String> ();
       Map < Id, Opportunity > mapIdOppRecords = new Map < Id, Opportunity > ();
       for (Opportunity Op: [SELECT Id,OppLineItemsCount__c,  (SELECT Id FROM Opportunitylineitems) FROM Opportunity WHERE
         StageName IN: mapOCRRequiredStages.keySet() AND Status__c != 'Closed'
         AND
         Opportunity.recordtype.Name IN: mapOCRRequiredRecordTypes.keyset() AND
         Id NOT IN(SELECT Opportunityid FROM OpportunityContactRole) AND Id IN: newOppsForUpdate
        ]) {
        if ((mapOldOpps.get(Op.id).OppLineItemsCount__c == op.Opportunitylineitems.size() && op.opportunitylineitems.size() > 0)) {
         system.debug('----Inside If------OCR');
         mapIdOppRecords.put(op.id, op);
        }
       }
       for (opportunity Op: newOppsForUpdate) {
        system.debug('Op.recordtype: ' + Op);
        if (mapIdOppRecords.get(Op.Id) != NULL) {
         Op.addError(' Contact Role is Mandatory ');
        }
       }

      } // End of isOCRfirstRun If condition
     } // End of ValidateOCR_OnOppStageChange
    } // End of OpportunityValidator
  • Why do you have a status__c field on the opportunity? why not use StageName? – PartOfTheOhana Jun 12 '19 at 16:45
4

You only have one query, so the culprit is pretty clear:

SELECT Id,OppLineItemsCount__c,  (SELECT Id FROM Opportunitylineitems) 
FROM Opportunity 
WHERE StageName IN: mapOCRRequiredStages.keySet() 
     AND Status__c != 'Closed'
     AND Opportunity.recordtype.Name IN: mapOCRRequiredRecordTypes.keyset() 
     AND Id NOT IN (SELECT Opportunityid FROM OpportunityContactRole) 
     AND Id IN: newOppsForUpdate

The error message is telling you that this query is not being treated as selective; the platform will always terminate non-selective queries executed against objects with data volume > 200,000 records when run in trigger context (directly or indirectly). This helps preserve performance of the trigger and shorten transaction time.

You should take some time to read Make SOQL query selective from the knowledge base, as well as the articles linked from there. Selectivity can be complex to figure out and can depend as well on the content of the variables being bound in the query and the configuration of the database, such as whether specific fields are indexed or External Ids.

My theory is that since your Id IN :newOppsForUpdate should be selective (assuming that collection contains no nulls and is of reasonable size), it's actually the unfiltered semi-join subquery getting you in trouble:

AND Id NOT IN (SELECT Opportunityid FROM OpportunityContactRole) 

Presumably there's more than 200,000 records in OpportunityContactRole, making this subquery non-selective.

I may be wrong; there's a lot of complexity to LDV SOQL optimization and I'm not a great expert in it.

However, I think there is a quite easy fix if my surmise is correct or close. Assuming, again, that newOppsForUpdate is of reasonable size (I'm presuming it comes from a trigger), run some of these filters in Apex rather than SOQL.

In particular, I would suggest you use a subquery to get the OpportunityContactRoles list for each queried Opportunity, in the same way that you already do with OpportunityLineItems. You would delete your ID NOT IN() clause.

You can then write Apex logic within the loop to exclude those Opportunity records having them, rather than using a semi-join. For example, inside the loop you might check if (Op.OpportunityContactRoles.size() > 0) continue; to skip those records.

I suspect that will cure the issue. I can't promise it, but it's a reasonable guess.

| improve this answer | |
  • Hi David: As you told i tried to execute the logic in Anonymous window it worked fine. when I tried to create/update a single record on opportunity it is throwing me this error [even for one record] – Muzeeb Jun 12 '19 at 15:23
  • @Muzeeb The error you are receiving will only take place in trigger context. – David Reed Jun 12 '19 at 15:25
  • Hi @David: Then how to modify my query in this case to avoid the error.? any suggestions please – Muzeeb Jun 12 '19 at 15:31
  • 1
    @Muzeeb My answer contains a specific suggestion. Did you try it? – David Reed Jun 12 '19 at 15:35
  • 1
    It worked for me , thank you very much David. – Muzeeb Jun 12 '19 at 16:05
0

Just to add few points to David's answer, I would mention some extra things

SELECT Id,OppLineItemsCount__c,  (SELECT Id FROM Opportunitylineitems) 
FROM Opportunity 
WHERE StageName IN: mapOCRRequiredStages.keySet() 
     AND Status__c != 'Closed'
     AND Opportunity.recordtype.Name IN: mapOCRRequiredRecordTypes.keyset() 
     AND Id NOT IN (SELECT Opportunityid FROM OpportunityContactRole) 
     AND Id IN: newOppsForUpdate

!= SOQL is very expensive, thus Status__c != 'Closed' is slowing things down.

Instead, create a Custom Formula Field isStatusClosed__c as Boolean and ask Salesforce Support to Index it for you. As its deterministic formula, they will be able to index it.

Also Opportunity.recordtype.Name IN: mapOCRRequiredRecordTypes.keyset() is again a join, instead in SOQL use recordTypeId

Opportunity.recordtypeId IN: mapOCRRequiredRecordTypesId.keyset()

| improve this answer | |

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.