+ Start a Discussion
Always ThinkinAlways Thinkin 

How to avoid QueryException over 100,000 records

I received the error message below and I think I fixed it but I have no way to test it (I only have a developer sandbox and I can't add 100,000 records to it).

 

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)

 

Originally, my query was:

SELECT Id, OwnerId FROM Lead WHERE IsConverted = FALSE AND            Lead_Temperature__c = NULL AND OwnerId IN :idScheduledStaff];

 

I adjusted it to remove the NULL value reference by listing all the possible picklist values:

 

SELECT Id, OwnerId FROM Lead WHERE IsConverted = FALSE AND
Lead_Temperature__c NOT IN ('Cold', 'Warm', 'Hot', 'Dead') AND OwnerId IN :idScheduledStaff];

 

I had already deleted enough records in production to get below 100,000 so that our Lead Generation would resume while I puzzled this out. I deployed the changed code to production and now I'm waiting to see what happens when we go above 100,000 again.

 

 

Can anyone confirm whether inverting the NULL condition is sufficient to avoid the exception?

Shilpa_SFShilpa_SF

Hi Luke,

 

               Its not about inverting the Null condition, If the data exceeds then you might face this error, make sure that you are querying only the selective data instead of using generic queries.