function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Neeraja SuribhatlaNeeraja Suribhatla 

: Non-selective query against large object type (more than 200000 rows

Hi,
This code has been written by previous developer. I would really appreicate if someone helps me with this error. Thank you.
  : execution of AfterInsert
 
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)

trigger AssessmentAnswers on Assessment__c (before insert,before update,after insert) {
    Map<String,List<List<List<String>>>> questionsMap=AssessmentTemplate.getQuestionMap();
    if(Trigger.isInsert && Trigger.isBefore){
        Map<String,Id> rtMap=new Map<String, Id>();
        List<RecordType> rtList=[select id,name from recordtype where name in ('Postsession','Presession','Dass','HolmesRahe')];
        For(RecordType rt:rtList){
            rtMap.put(rt.name,rt.id);
        }
        List<Assessment__c> assList=trigger.new;
        For(Assessment__c ass : assList){
            if(ass.Type__c!=null  && rtMap.containsKey(ass.Type__c.split('::')[1])){
                ass.RecordTypeId=rtMap.get(ass.Type__c.split('::')[1]);
            }
            Integer i=1;
            List<List<List<String>>> assTemplate=questionsMap.get(ass.Type__c);
            if(assTemplate!=null && ass.Answers__c!=null){
                For(String answer : ass.Answers__c.split(',')){
                    if(answer.isNumeric() && i<=assTemplate.size() && Integer.valueof(answer)<assTemplate[i-1][1].size()) { 
                        ass.put('Answer_'+i+'__c',assTemplate[i-1][1][Integer.valueof(answer)]);
                        ass.put('Answer_Value_'+i+'__c',Integer.valueof(answer));
                        ass.put('Question_'+i+'__c',assTemplate[i-1][0][0]);
                    }
                    i++;
                }
            }
        }
    }else if(Trigger.isInsert && Trigger.isAfter){
        AssessmentTemplate.cleanupAssessments();
    }else if(Trigger.isUpdate && Trigger.isBefore){
        List<Assessment__c> assList=trigger.new;
        For(Assessment__c ass : assList){
            Integer i=1;
            List<List<List<String>>> assTemplate=questionsMap.get(ass.Type__c);
            if(assTemplate!=null && ass.Answers__c!=null){
                For(String answer : ass.Answers__c.split(',')){
                    if(answer.isNumeric() && i<=assTemplate.size() && Integer.valueof(answer)<assTemplate[i-1][1].size()) { 
                        ass.put('Answer_'+i+'__c',assTemplate[i-1][1][Integer.valueof(answer)]);
                        ass.put('Answer_Value_'+i+'__c',Integer.valueof(answer));
                        ass.put('Question_'+i+'__c',assTemplate[i-1][0][0]);
                    }
                    i++;
                }
            }
        }
    }
}
Dev_AryaDev_Arya
Hi Neeraja,

There is only one line of code for after insert 
}else if(Trigger.isInsert && Trigger.isAfter){
    AssessmentTemplate.cleanupAssessments();
}
Could you please post the contents of this function. Then we can see which field can be indexed.
On the otherhand, the probable cause of the error is:
That it is costing system too much of time to finish the job and eating more resources. According to SF best practoces, when we need to process large number of records, implement a future method or get it done through Batch Apex. Future method provides more CPU time per job in comparion to the sysnchronous calls (60 secs for future method and 10 seconds for sysnchronous job) and also more SOQL queries. 

Hoep this helps. And if it does, please mark the solution as green.

Cheers n happy coding,
Dev
Neeraja SuribhatlaNeeraja Suribhatla
Hi Dev,
Here is the function of AssessmentTemplate. Can please help me with this. Thank you.


    public static void cleanupAssessments(){
      Integer maxBTID=100000;
        Map<String,List<Assessment__c>> appMap=new Map<String,List<Assessment__c>>();
        For(Assessment__c ass : [SELECT Id,Appointment_GUID__c,Provider_Id__c,Account_Id__c  FROM Assessment__c WHERE BT_ID__c>:maxBTID and Appointment__c=null AND Appointment_GUID__c!=null AND Appointment_GUID__c!='' AND Appointment_GUID__c!='0' ORDER BY BT_ID__c]){
            Map<String,List<Assessment__c>> tempMap=appMap;
           List<Assessment__c> tempList = null;
            if (tempMap.containsKey(ass.Appointment_GUID__c)) {
                tempList = tempMap.get(ass.Appointment_GUID__c);
                if(tempList == null)
                    tempList = new List<Assessment__c>();
                tempList.add(ass);  
            } else {
                tempList = new List<Assessment__c>();
                tempList.add(ass);               
            }
            tempMap.put(ass.Appointment_GUID__c,tempList);
        } 
        system.debug('Appointment Map size: '+appMap.size());
        List<Assessment__c> assUpdateList=new List<Assessment__c>();
        For (Appointment__c app : [SELECT Id,BT_Guid__c,Client_Name__c,Provider_Name__c FROM Appointment__c WHERE BT_Guid__c IN :appMap.keySet()]){
            List<Assessment__c> assList=appMap.get(app.BT_Guid__c);
            For (Assessment__c ass : assList){
                ass.Appointment__c=app.id;
                ass.Provider_Name__c=app.Provider_Name__c;
                ass.Client_Name__c=app.Client_Name__c;
            }
            assUpdateList.addAll(assList);
        } 
        system.debug('Assessments to update: '+assUpdateList.size());
        update assUpdateList;  
        
        Map<Decimal,List<Assessment__c>> providerMap=new Map<Decimal,List<Assessment__c>>();
        For(Assessment__c ass : [SELECT Id,Appointment_GUID__c,Provider_Id__c,Account_Id__c  FROM Assessment__c WHERE BT_ID__c>:maxBTID and Provider_Name__c=null AND provider_id__c>0 ORDER BY BT_ID__c]){
            Map<Decimal,List<Assessment__c>> tempMap=providerMap;
            List<Assessment__c> tempList = null;
            if (tempMap.containsKey(ass.provider_id__c)) {
                tempList = tempMap.get(ass.provider_id__c);
                if(tempList == null)
                    tempList = new List<Assessment__c>();
                tempList.add(ass);  
            } else {
                tempList = new List<Assessment__c>();
                tempList.add(ass);               
            }
            tempMap.put(ass.provider_id__c,tempList);
        } 
        system.debug('Provider Map size: '+providerMap.size());
        assUpdateList.clear();
        For (Contact c : [SELECT Id,BT_Id__c FROM Contact WHERE RecordTypeId='01215000001Ys3LAAS' AND BT_Id__c in :providerMap.keySet()]){
            List<Assessment__c> assList=providerMap.get(c.BT_Id__c);
            For (Assessment__c ass : assList){
                ass.Provider_Name__c=c.id;
            }
            assUpdateList.addAll(assList);
        } 
        system.debug('Assessments to update: '+assUpdateList.size());
        update assUpdateList;
        
        Map<Decimal,List<Assessment__c>> clientMap=new Map<Decimal,List<Assessment__c>>();
        For(Assessment__c ass : [SELECT Id,Appointment_GUID__c,Provider_Id__c,Account_Id__c  FROM Assessment__c WHERE BT_ID__c>:maxBTID and Client_Name__c=null AND account_id__c>0 ORDER BY BT_ID__c]){
            Map<Decimal,List<Assessment__c>> tempMap=clientMap;
            List<Assessment__c> tempList = null;
            if (tempMap.containsKey(ass.account_id__c)) {
                tempList = tempMap.get(ass.account_id__c);
                if(tempList == null)
                    tempList = new List<Assessment__c>();
                tempList.add(ass);  
            } else {
                tempList = new List<Assessment__c>();
                tempList.add(ass);               
            }
            tempMap.put(ass.account_id__c,tempList);
        }
        system.debug('Client Map size: '+clientMap.size());
        assUpdateList.clear();
        For (Contact c : [SELECT Id,BT_Id__c FROM Contact WHERE RecordTypeId='01215000001Ys3HAAS' AND BT_Id__c in :clientMap.keySet()]){
            List<Assessment__c> assList=clientMap.get(c.BT_Id__c);
            For (Assessment__c ass : assList){
                ass.Client_Name__c=c.id;
            }
            assUpdateList.addAll(assList);
        }
        system.debug('Assessments to update: '+assUpdateList.size());
        update assUpdateList;
    }
NForceNForce
Hi Neeraja,
Try to add extra WHERE clause with indexed fields likes lookup relationship, master-details relationship, created date, last modified date.

If the filter is on a standard field, it'll have an index if it is a primary key (Id, Name, OwnerId), a foreign key (CreatedById, LastModifiedById, lookup, master-detail relationship), and an audit field (CreatedDate,   SystemModstamp).
Custom fields will have an index if they have been marked as Unique or External Id
If you want to do a custom index on a field, create a support case with Salesforce, they will do it for you.