+ Start a Discussion
GMASJGMASJ 

more than 200000 rows

Hi, 

   In below code class CaseTriggerUtils
 
trigger CaseTrigger on Case (Before Insert, Before Update,After Insert, After Update){


   if(Trigger.isBefore) {
      CaseTriggerUtils.CaseUtils(Trigger.new);     
   } 
      

}
There is a error in line  
Map<Id, case> c = new Map<Id, case>([select id, casenumber,subject,createddate,ownerid,owner.name from case where subject like :('%' + CaseSubject + '%') order by createddate asc limit 10]);

When i try to update the case i am getting below error. 

Error: Invalid Data. 
Review all error messages below to correct your data.
Apex trigger CaseTrigger caused an unexpected exception, contact your administrator: CaseTrigger: 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.CaseTriggerUtils.CaseUtils: line 31, column 1
public class CaseTriggerUtils {

 public static void CaseUtils(List<Case> newLst) {  
  
  String FWSubject;
  String RWSubject;
  String CaseSubject;
  
    for (Case c : newLst) {
       system.debug('Case Number :' + c.casenumber); 
       system.debug('Case Subject : ' + c.subject);
       system.debug('Case Subject length : ' + c.subject.length());
       system.debug('Case Owner Name : ' + c.owner.name);
       
       CaseSubject = c.subject;
       
       //Search for FW and RW key word were length of the subject is more than 18
        if(c.subject != null && c.subject.length() > 18){
            if(!c.subject.Contains('FW:')){
              FWSubject = c.subject.replaceAll('FW:','');
              System.debug('FQ Trim String :' + FWSubject);     
         }  
    
        if(!c.subject.Contains('RW:')){ 
         RWSubject = c.subject.replaceAll('RW:','');
         System.debug('RW Trim String :' + RWSubject);   
         }     
       }  
     }
     
   Map<Id, case> c = new Map<Id, case>([select id, casenumber,subject,createddate,ownerid,owner.name from case where subject like :('%' + CaseSubject + '%') order by createddate asc limit 10]);
   
     
   //Search for same case based on subject within 10days. 
   /* list<case> ctendays = [select id, casenumber,subject,createddate,ownerid,owner.name from case where subject like :('%' + CaseSubject + '%') order by createddate asc limit 10];

   for(case ctndays : ctendays){
      system.debug('Existing Cases :'  + ctndays.casenumber + ' ' + ctndays.subject + ' ' + ctndays.createddate + ' ' + ctndays.owner.name);      
   }  */
  
  }

}

 
Best Answer chosen by GMASJ
Khan AnasKhan Anas (Salesforce Developers) 
Hi,

Greetings to you!

According to Salesforce Knowledge Article (https://help.salesforce.com/articleView?id=000002493&type=1):

1. You may find that the query in question needs to be more selective in the WHERE clause. According to the Salesforce standards & best practices - the where clause needs to subset 10% or less of the data.

2. A custom index (https://help.salesforce.com/articleView?id=000232453&type=1&language=en_US) on the field.

3. A possible quick fix may be to make the field in question an external ID. Since external IDs are indexed automatically, this will create the index and may solve the problem.

In Classic, go to Setup -> Create -> Objects then click the Object name. 
In LEX, go to Setup -> Object Manager  then Object name.
Then, edit field name and check the box External ID.

NOTE: If this does help your query performance, we recommend that you still log a case with support to implement a custom index in it's place. This is not a long term solution for performance improvements and has other designed use cases.

Read Make SOQL query selective (https://help.salesforce.com/articleView?id=000006044&type=1) help article for more information on indexing.

https://help.salesforce.com/articleView?id=000006007&language=en_US&type=1

I hope it helps you.

Kindly let me know if it helps you and close your query by marking it as solved so that it can help others in the future. It will help to keep this community clean.

Thanks and Regards,
Khan Anas

All Answers

Khan AnasKhan Anas (Salesforce Developers) 
Hi,

Greetings to you!

According to Salesforce Knowledge Article (https://help.salesforce.com/articleView?id=000002493&type=1):

1. You may find that the query in question needs to be more selective in the WHERE clause. According to the Salesforce standards & best practices - the where clause needs to subset 10% or less of the data.

2. A custom index (https://help.salesforce.com/articleView?id=000232453&type=1&language=en_US) on the field.

3. A possible quick fix may be to make the field in question an external ID. Since external IDs are indexed automatically, this will create the index and may solve the problem.

In Classic, go to Setup -> Create -> Objects then click the Object name. 
In LEX, go to Setup -> Object Manager  then Object name.
Then, edit field name and check the box External ID.

NOTE: If this does help your query performance, we recommend that you still log a case with support to implement a custom index in it's place. This is not a long term solution for performance improvements and has other designed use cases.

Read Make SOQL query selective (https://help.salesforce.com/articleView?id=000006044&type=1) help article for more information on indexing.

https://help.salesforce.com/articleView?id=000006007&language=en_US&type=1

I hope it helps you.

Kindly let me know if it helps you and close your query by marking it as solved so that it can help others in the future. It will help to keep this community clean.

Thanks and Regards,
Khan Anas
This was selected as the best answer
abhishek singh 497abhishek singh 497
Hello,
Yes, that means it's taking too long.  A couple of ways to get more CPU time are to put the code in an @future method (you get 60 seconds of CPU instead of just 10 seconds) or implement it as a Batch Apex job.  Since both of these methods are asynchronous, the trigger and associated DML will complete before the calls to the AccountServices methods are made - but they will be called eventually.  Would this work for you?

Please mark it as the best one, if it solves your problem.
Thanks & Regards,
Abhishek Singh.
SEKAR RAJ.SEKAR RAJ.
Hi GMASJ,
When querying the records from the Object which has more than 200000 records, then you will get the error "System.QueryException: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. "
The Best way to avoid this, to add a filter condition to restrict the records.
Created date > LAST_YEAR
You can add the filter conditions based on your logic.

Thanks,
SEKAR RAJ
GMASJGMASJ
Thanks you all for you help and suggestion.