+ Start a Discussion
TehNrdTehNrd 

What is the definitive answer on non-selective query?

There are several posts on here regarding the infamouse non-selective query error but it has never really been clearly addressed from salesforce.com. Here is the 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)


Here is a query causing this error:

for(Opportunity opp : [select Id from Opportunity where EvalCondSBSO__c = '123456']){
    system.debug(opp);
}

 

This error doesn't make sense because it does not seem relevant. For starters, the query is selective. Also:
1) The filter value does not include nulls
2) The number of match rows returned would be very minimal.

The only thing that even starts to make sense is the Opportunity table has 250,000 rows. So really, is there simply a limitation that triggers cannot query tables with more than 100K rows? If so, this should be documented somewhere.

Thanks,
Jason

 

David SchachDavid Schach

This is a good moment to ask when the VLO flag should be requested for an org.  Although 250k records doesn't sound like a lot, is it over the threshold for VLO status?  (VLO means very large organizations, and was the setting reserved for orgs that had reporting slow-downs due to so many records.)

I realize that querying and reporting are similar but not identical, but it seems an apropos time to ask.

Ritesh AswaneyRitesh Aswaney

I'm wondering if anything changes if you make EvalCondSBSO__c an ExternalId field and include it in the select ?

 

for(Opportunity opp : [select Id, EvalCondSBSO__c from Opportunity where EvalCondSBSO__c = '123456']){
    system.debug(opp);
}
bob_buzzardbob_buzzard

I'd go along with Ritesh on this.  

 

If your field isn't an external id (or you haven't asked Salesforce to create an index on it), then it won't be indexed in the database, which means that the the entire table would need to be traversed.  

 

I know for a fact that this is an expensive operation for Salesforce as one of our customers had a SOQL query in apex code that traversed a 50k row table around 1000 times and they received an email from support telling them they were responsible for over a quarter of the total CPU used that day!

 

gaisergaiser

Jason,

1. As previous commenters said: make sure your EvalCondSBSO__c field is indexed
----- either mark it as external ID
----- or ask SFDC support to index the field

2. 10% rule
----- a filter is said to be selective if it returns less than 10% of the total amount of records.
----- i.e. for the table of size 250K the selectivity threshold would be 25,000

TehNrdTehNrd

Gaiser,

 

Thanks, finally the level of detail that has been shrouded in secrecy, do you work at SFDC or have good sources on the 10% rule?

 

Yup, well alware of the external Id trick and this is what I've done but the field isn't truly an external Id so it is sort of a hack for now.

 

10% rule:

Finally some detail! Techincally my query only returns 1 record but it must look at entire table to determine this. What types of filters reduce the total amount of records queuried. The only thing that appears to work is filtering on dates. Are there anyother filters that can be used to make a query more selective.

 

Thanks,

Jason

gaisergaiser

Jason

 

>>have good sources on the 10% rule?
This information is from SFDC support (we had similar case with them, escalated to Tier 3)


>>Techincally my query only returns 1 record
If that's actually the case (i.e. there is exactly 1 matching record and you do not just do 'limit 1') then it must be selective query.
1 record out of 250K is well under 10%

You can try to escalate with SFDC support as well.
I have had couple of such (non-selective query) cases in the past and in one instance error just went away by itself after we escalated with SFDC and the other case exceeded 10% limit so had to find alternative way to implement required.

TehNrdTehNrd

Thanks Gaiser,

 

Adding a limit clause has no affect. The query still has to look at the entire table and will simply stop after 1 or 500 or whatever you define.

 

So the outstanding question now is what type of filter criteria can be applied to make a query more selective?

 

I currently have a case one that I believe is very close to tier 3 and I will see if they can answer this.

 

-Jason

TehNrdTehNrd

I am not so sure the 10% rule is correct. We have over 300K contacts but I can run a query like this with no issues.

 

List<Contact> cons = [select Id from Contact where Email = 'asdfasd@asdfasd.com'];

Are certains fields automatically indexed?

 

gaisergaiser

Have you had any luck with tier 3 support?

 



TehNrd wrote:

I am not so sure the 10% rule is correct. We have over 300K contacts but I can run a query like this with no issues.

 

List<Contact> cons = [select Id from Contact where Email = 'asdfasd@asdfasd.com'];

 


 

How many records this query returns?

If it is anything less than 30K then it is within the 10% total table size limit.

Besides - as I mentioned - the 10% rule info is not my imagination, we got it from SFDC support and in our case this was the only explanation why query (all fields indexed, filter does not contain nulls) fails returning more than 10% table records.


TehNrd wrote:
Are certains fields automatically indexed?

 


I am pretty sure that certain standard fields (ex. Name) as well as custom Lookup fields are indexed automatically.

Taking into account that Email (standard filed) is one of the key fields I would not be surprised if it is indexed out of the box.