0

I have created an app that would change CampaignMemeber Status Values by calling our web application api and storing info in custom objects. In my developer org where the package is created works fine but on our clients sandbox they get the error

System.QueryException: Non-selective query against large object type (more than 200000 rows).

I know the reason that they get is because the query is not selective or that they have a lot of record. The error is occurring in a trigger. Below i have my trigger. Could anyone suggest a way to make my query more selective(i know about indexed field and tire that already) or any other way around the issue?

 trigger SynchronizeTrigger on Attendee__c (after update, after insert) {
     Map<String,String> matchedRegistration = new Map<String,String>();
     Map<String,String> matchedCheckin = new Map<String,String>();
     Set<String> matchedEmails = new Set<String>();
     Map<String,String> matchedSurnames = new Map<String,String>();

    CampaignMember[] cmList = new CampaignMember[]{};
     List<Fail__c> flist = new List<Fail__c>();



    Public Authentication__c auth = new Authentication__c();
    auth = [SELECT Authentication_Campaign__c FROM Authentication__c LIMIT 1];



    public Integer attendeesAttended = 0;
    public Integer attendeesDidNotShow = 0;
    public Integer attendeesAccepted= 0;
    public Integer attendeesCancelled = 0;
    public Integer attendeesfailed = 0;

    for(Attendee__c a : Trigger.new){
        matchedRegistration.put(a.Attendee_Email_Address__c,a.Attendee_Registration__c);
        matchedCheckin.put(a.Attendee_Email_Address__c,a.Attendee_Checkin__c); 
        matchedEmails.add(a.Attendee_Email_Address__c);
    }


    for(List<CampaignMember> cms : [SELECT Id,Campaign.Id, Contact.Email,Status
                           FROM CampaignMember
                           WHERE  Campaign.Id =: auth.Authentication_Campaign__c AND  Contact.Email  IN: matchedEmails]){

       for(CampaignMember cm : cms){

          if(matchedRegistration.get(cm.Contact.Email)=='1' && matchedCheckin.get(cm.Contact.Email)=='1'){

              cm.Status = 'Attended';
              cmList.add(cm);
              attendeesAttended++;

          }else if(matchedRegistration.get(cm.Contact.Email)=='1' && matchedCheckin.get(cm.Contact.Email)=='0'){

              cm.Status = 'Did Not Show';
              cmList.add(cm);
              attendeesDidNotShow ++; 

          }else if(matchedRegistration.get(cm.Contact.Email) =='1' && String.isEmpty(matchedCheckin.get(cm.Contact.Email))){

              cm.Status = 'Accepted';
              cmList.add(cm);
              attendeesAccepted ++;

          }else if(matchedRegistration.get(cm.Contact.Email)=='0'){

              cm.Status = 'Cancelled';
              cmList.add(cm);
              attendeesCancelled ++;       
         }

    update cmList;
      }
    } 

  for(List<CampaignMember> cms : [SELECT Id,Campaign.Id,FirstName,LastName,Contact.Email
                           FROM CampaignMember
                           WHERE Campaign.Id =: auth.Authentication_Campaign__c AND Contact.Email NOT IN: matchedEmails]){

            for(CampaignMember cm : cms){

            fList.add(new Fail__c(Fail_Firstname__c = cm.FirstName, Fail_Surname__c = cm.LastName, Fail_Email__c = cm.Contact.Email,Fail_Campaign_Name__c = cm.Campaign.Name ,Fail_Campaign__c = auth.Authentication_Campaign__c));
            attendeesfailed ++;
            upsert fList Fail_Email__c;
          }
  }


  ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.Confirm, 'Attended: ' + attendeesAttended + ' Delegate(s)' + ', ' + 'Did Not Show: ' + attendeesDidNotShow + ' Delegate(s)'+ ', ' + 'Accepted: ' + attendeesAccepted + ' Delegate(s)' + ', ' + 'Cancelled: ' + attendeesCancelled + ' Delegate(s)'));
  ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.Error, 'Failed Syncs: ' + attendeesfailed + ' Delegate(s)'));
}

Thanks

1
  • 1
    Avoid having DML operations (insert, update, delete) inside a for Loop since that will cause unnecessarily exceed the governor limit pertaining to DML operations.
    – vignesh
    Apr 18 '17 at 12:03
2

Few optimization you can do:-

  1. Add new filter Status in query. I see that you are processing certain status values only. So, we don't need all status.

  2. You can also narrow records by filtering records by adding filter field Contact.Email. You already have a list matchedRegistration, matchedCheckin etc.

  3. Contact.Email NOT IN: matchedEmails is the most expensive filter. See, if you can remove this.

  4. Also, you can always check the selectivity in Dev Console by enabling the "Enable Query Plan" in dev console's preference section. You can run different combination to identify the best query.
    enter image description here

If the query run through query plan has cost more that 1 then it is a problem for large Sobjects. enter image description here

Note: There are DMLs in for-loop. You must keep them outside of loop by leveraging collection types.

3
  • thank you for the reply. i havee tried some of your suggestions but still the same error occurs.i have noticed that the table am querying could have more than 500000 records.
    – Kyri Z.
    Apr 18 '17 at 13:57
  • @KyriZ. If you have run out of filters which can be applied to the query and, you will be getting more than 50k records then you will have to consider batch processing only.
    – Ashwani
    Apr 18 '17 at 14:21
  • thats what i was thinking a well!thanks for the advice.
    – Kyri Z.
    Apr 18 '17 at 14: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.