+ Start a Discussion
sunny.sfdcsunny.sfdc 

non selective query failing in production only

Hi All

I have following query in my code:
[select abc__c,xyz__c from sobj where field1__c != null and field2__c IN :lstValues]

field1__c is a text field
field2__c is a formula field which is a combination of 3 custom fields separated by #

I am experiencing following error when the trigger executing the above mentioned query runs in production:
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)

However this is working perfectly fine in full sandbox. My production environment has 146000 rows and my full sandbox has 200000 rows.
If this is working in full sandbox this should work in production as well as it is having low volume of data.

Please help!

Regards
Sachin
Best Answer chosen by sunny.sfdc
Amit Chaudhary 8Amit Chaudhary 8
Please check below post how to resolve this issue
1) https://help.salesforce.com/apex/HTViewSolution?urlname=How-to-make-my-SOQL-query-selective&language=en_US

Force.com SOQL Best Practices: Nulls and Formula Fields
1) https://developer.salesforce.com/blogs/engineering/2013/02/force-com-soql-best-practices-nulls-and-formula-fields.html

1. You may find that the query in question needs to be more selective in the WHERE clause. I.e. as per Salesforce standards & best practices - the where clause needs to subset 10% or less of the data.
2. You may need a custom index 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.

To make the field an external ID simply go to Setup | Customize | Object name | Fields, edit field name. and check the box "External ID".