+ Start a Discussion
kkr.devkkr.dev 

Non-selective query against large object type (more than 100000 rows).

FATAL_ERROR|System.QueryException: Non-selective query against large object type (more than 100000 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 InsertFin  on Case (after insert) {
 list<Fin__c> falist = new list<Fin__c>();
 
  if ((Trigger.new.size() == 1)&&(trigger.new[0].MID__c !=null)){
      list<Contact> con = new list<Contact>([Select  c.EmpId__c,c.branch__c,c.branch__r.name,c.Id,c.name From Contact c where c.EmpId__c!=null and c.EmpId__c =: trigger.new[0].MID__c limit 1]);
      Fin__c f1 = new Fin__c();
        f1.Case__c = trigger.new[0].Id;
        if(con.size()>0){
        f1.Branch__c = con[0].branch__r.name;
        f1.name= con[0].name;
        }
        falist.add(f1);
      }
  if ((Trigger.new.size() == 1)&&(trigger.new[0].MID_2__c !=null)){
      list<Contact> con = new list<Contact>([Select  c.EmpId__c,c.branch__c,c.branch__r.name,c.Id,c.name From Contact c where c.EmpId__c!=null and c.EmpId__c =: trigger.new[0].MID_2__c limit 1]);
      Fin__c f2 = new Fin__c();
        f2.Case__c = trigger.new[0].Id;
        if(con.size()>0){
        f2.Branch__c = con[0].branch__r.name;
        f2.name= con[0].name;
        }
        falist.add(f2);
      }
      
  if ((Trigger.new.size() == 1)&&(trigger.new[0].MID_3__c !=null)){
      list<Contact> con = new list<Contact>([Select  c.EmpId__c,c.branch__c,c.branch__r.name,c.Id,c.name From Contact c where c.EmpId__c!=null and c.EmpId__c =: trigger.new[0].MID_3__c limit 1]);
      Fin__c f3 = new Fin__c();
        f3.Case__c = trigger.new[0].Id;
        if(con.size()>0){
        f3.Branch__c = con[0].branch__r.name;
        f3.name= con[0].name;
        }
        falist.add(f3);
      }
      
      if ((Trigger.new.size() == 1)&&(trigger.new[0].MID_4__c !=null)){
      list<Contact> con = new list<Contact>([Select  c.EmpId__c,c.branch__c,c.branch__r.name,c.Id,c.name From Contact c where c.EmpId__c!=null and c.EmpId__c =: trigger.new[0].MID_4__c limit 1]);
      Fin__c f4 = new Fin__c();
        f4.Case__c = trigger.new[0].Id;
        if(con.size()>0){
        f4.Branch__c = con[0].branch__r.name;
        f4.name= con[0].name;
        }
        falist.add(f4);
      }
      if ((Trigger.new.size() == 1)&&(trigger.new[0].MID_5__c !=null)){
      list<Contact> con = new list<Contact>([Select  c.EmpId__c,c.branch__c,c.branch__r.name,c.Id,c.name From Contact c where c.EmpId__c!=null and c.EmpId__c =: trigger.new[0].MID_5__c limit 1]);
      Fin__c f5 = new Fin__c();
        f5.Case__c = trigger.new[0].Id;
        if(con.size()>0){
        f5.Branch__c = con[0].branch__r.name;
        f5.name= con[0].name;
        }
        falist.add(f5);
      }
      insert falist;
      
      
}

How to fix this trigger.Any Idea?

 

 

Thanks


BritishBoyinDCBritishBoyinDC

Try removing this part of the SOQL:

c.EmpId__c!=null

 

I don't see why you need it, and that is almost certainly the cause of the error... 

AlphaPAlphaP

In case you haven't seen this - the link below got me through this error.   BritishBoyinDC is right - !=null automatically makes the statement non-selective.   If you still get the error, try adding indexed fields in the WHERE clause and other requirements to narrow down results.   I often use modified dates since they are indexed.

 

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

kkr.devkkr.dev

BritishBoyinDC Thanks for reply.I still get the same error even after removing  c.EmpId__c!=null from SOQL

kkr.devkkr.dev

We have already exceeded limit 3 for the object.So i can't  index the field .Any Idea?

AlphaPAlphaP

First, run the query yourself - maybe in reports and see how many records you get.  Are you pulling more than 100,000?

 

Outside of the three custom field inexed, Salesforce has several other 'canned' fields that are indexed automatically.   See if you can refernce some of these to narrow down your results.   See below:

 

Fields that are always indexed: CreatedDate, Id, Name, Owner, RecordType, SystemModstamp, Lookup Fields (Unless Specified)

Fields that are never indexed: CreatedBy (Unless Specified), LastModifiedBy

Additional fields indexed on every standard object:

- Activities: ActivityDate, CreatedBy, Status, What, Who

- Assets: SerialNumber

- Campaign: IsActive

- Contacts: Email

- Cases: CaseNumber, ClosedDate

- Contracts: ContractNumber, CustomerSigned, EndDate

- Ideas: Title

- Leads: Company, Email

- Opportunities: CloseDate

- Opportunity Products: Date

- Products: ProductCode
- Solutions: SolutionName

 

  •  On top of these, system time fields (last modified, created date etc) and any lookup fields are also indexed.



For more info please refer below mentioned link:-
http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm