7

I am trying to query a Custom Object called Lead_Assignment__c based on some criteria. Original query below:

List<Lead_Assignment__c> leadAssignments = [
    SELECT Segment__c, Queue_Name__c, Zip_Code__c FROM Lead_Assignment__c
    WHERE (Segment__c = :MID_MARKET_SEGMENT AND Zip_Code__c IN :midMarketZipCodes)
    OR (Segment__c = :ENTERPRISE_SEGMENT AND Zip_Code__c IN :enterpriseZipCodes)
];

I populate midMarketZipCodes and enterpriseZipCodes based on Leads passed in from my Lead trigger. When I try to save one lead (Enterprise), I get 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 (for instance, filtering for a particular foreign key value that
occurs many times)

So I broke out the query to debug easier:

String soqlQuery= 'SELECT Segment__c, Queue_Name__c, Zip_Code__c FROM Lead_Assignment__c' +
    ' WHERE (Segment__c = :ENTERPRISE_SEGMENT AND Zip_Code__c IN :enterpriseZipCodes)';
system.debug(soqlQuery);
system.debug(ENTERPRISE_SEGMENT);
system.debug(enterpriseZipCodes);
List<Lead_Assignment__c> leadAssignments = baseQueryClause + enterpriseClause;
/*
* Results in:
* SELECT Segment__c, Queue_Name__c, Zip_Code__c FROM Lead_Assignment__c
* WHERE (Segment__c = :ENTERPRISE_SEGMENT AND Zip_Code__c IN :enterpriseZipCodes)
* Enterprise
* 93502
*/

I copy-pasted this Query into the Query Editor and substituted in 'Enterprise' and '93502'. It returned one result, exactly what I was expecting. I substituted those values into soqlQuery so the query was identical to what I entered in the Query Editor, and I still get the error.

Final Query:

'SELECT Segment__c, Queue_Name__c, Zip_Code__c FROM Lead_Assignment__c
WHERE (Segment__c = \'Enterprise\' AND Zip_Code__c = \'93502\')'
  • 1
    just a guess, Is one of the fields segment__c (or) Zip_code__c an indexed field? If not can you try adding name !=null.The following fields are indexed by default: primary keys (Id, Name and Owner fields) salesforce.com/us/developer/docs/apexcode/Content/… – Rao Sep 12 '13 at 20:26
  • @rao I believe using != will totally skip any index on the table and should be avoided where possible in soql. – Samuel De Rycke Sep 13 '13 at 9:47
  • I would think so too, but in this case it is counting that index in the WHERE clause. Also, != is obviously bad when it is your only filter criterion, but in conjunction with other criteria it can be quite useful. – Adrian Larson Sep 13 '13 at 16:16
6

So...this is a strange result but it works. It seems that when an sObject has more than 100,000 records, queries on this object need a WHERE clause that references an indexed field, even if that reference does not affect the results. The docs state:

A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.

However, the claim that the filter must reduce the resulting number of rows does not seem to be accurate. Before and after I add that clause, I get one result.

  • Any update on this? Did you resolve? I'm stuck with the same problem. I've more than 100,000 records but the result set is very less(meagre). The reply from Salesforce support is to reduce the number of rows it returns. :( The query by default has an indexed field in the filter(a lookup). – Vignesh May 5 '14 at 11:02

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.