3

I have my own custom code(before insert trigger) for check on Dead Leads. for storing dead Leads there is a custom object. Currently the custom object has more than 200000 records. Due to large number of records, while querying against the custom object, i am getting the following exception.

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    

Can we handle this exception using some custom code or can we handle this issue in pure salesforce way.

following is the query being used

for(Dead_Leads__c ld:[select unique_Id__c from Dead_Leads__c where unique_Id__c IN :UnqIdSet])
  • 1
    Can you check if your UnqIdSet has any values or its null – Prady Dec 17 '13 at 11:28
  • 1
    Thats good point @Prady.One more thought is to get indexed unique_Id__c field by raising a case with customer support – Mohith Shrivastava Dec 17 '13 at 11:29
  • 1
    If unique_Id__c is a lookup, then its already indexed, you wouldnt need to get it indexed as @MohithKumar suggested, otherwise you might have to – Prady Dec 17 '13 at 11:31
  • hi prady, i have checked for null presence. the error occurs even when the set is having values – Bharadwaj Urma Dec 17 '13 at 12:06
  • Try @eyescream suggestion of marking the field as externalid for indexing. This error generally happens when salesforce needs to do full table scan. – Prady Dec 17 '13 at 13:28
4

Mark the field as External Id (even if it isn't unique, you don't have to tick the "Unique" checkbox next to it), give it a while to run the indexing and retry the query. Marking as ext. id is a cheap trick to get something indexed before you have to contact support. You'll notice that index works when searching (in the sidebar or the global search if you have Chatter) for one of the values you know is there will start bringing the Dead Lead records.

Use LIMIT clause (how many do you reasonably expect to process in 1 go? 1K? 10K? There's really no need to return the whole 50K or bigger set before you hit limits)

Last but not least - Prady's comment:

Can you check if your UnqIdSet has any values or its null

Try running a query (or maybe a report would be better) that will give you info how many Dead_Leads__c have unique_Id__c = null.


Edit after comment that it's a formula field

Uuuh, that means SF has to do a full table scan to evaluate the formula for each row. Can you "help" the database by adding some components of this formula to the WHERE clause? I don't know without looking at your data, anything like WHERE Name LIKE 'Smith%'...

Or you could try making a text field, marking it as Ext.Id and having a simple workflow rule that on every insert/update will copy the value of formula to the field.

Then simply run a data fix that will populate the field and eventually start using this field in queries. Something as simple as update [SELECT Id FROM Dead_Leads__c WHERE Unique_Id_Ext_Id__c = null LIMIT 10000]; run it couple times and ensure the workflow populates it (remember to activate the workflow first ;)).

Check also the Related questions that are displayed on the right. For example How to run SOQL for an Object having more than 100K records? contains a really helpful answer by @PJC.

| improve this answer | |
  • hi eyescream, i missed an important point that the unique_Id__c is a formula field. So making it external Id is not possible – Bharadwaj Urma Dec 17 '13 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.