function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
kurtz_wolfgangkurtz_wolfgang 

System.QueryException: Non-selective query against large object type (more than 100000 rows)

Hello Salesforce Gurus,

 

  I need help with this exception: System.QueryException: Non-selective query against large object type (more than 100000 rows)

 

  Following is the query where I get the exception: select Opportunity_Number__c,Id 

from Opportunity where Opportunity_Number__c in :LstOpptyNbrs

 

  Now Opportunity_Number__c is Text(255) (External ID) (Unique Case Sensitive). Salesforce suggests that if the dataset is large than the query should contain filters against indexed fields, which I am doing here. Moreover I get this exception when a record is sent from another SF instance in a SF - SF integration.

 

Thanks & Regards,

K

avijchakavijchak

Querymore can help

 

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_querymore.htm
kurtz_wolfgangkurtz_wolfgang

Thanks Avijit for your reponse, however the code that i posted is used in SOQL for loop, which internally uses QueryMore call.

 

check this: While the standard queries discussed in SOQL and SOSL Queries can retrieve either the count of a query or a number of object records, SOQL for loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of the SOAP API.

 

Moreover since the field is an external Id, there would be only one row for each value in the list used in the where clause.

 

Regards,

K

Salesforce.com Architect
CLKCLK
this index field also returning more than 10k records; thats y the issue. You should use another indexed field in query clause
kurtz_wolfgangkurtz_wolfgang

Hi Chetan,

 

  Can you explain a bit more about that? I thought that since the Opportunity_Number__c is a unique key/indexed, there would be only one opportunity for each Opportunity_Number__c. Moreover it won't do a full table scan when you use that field.

 

Thanks,

K
CLKCLK
For a single indexed field, Salesforce does not use the index if the number or records to be returned exceeds 10% of the total number of records in the object or 333,000 records.
This doc wil help u:

http://www.salesforce.com/us/developer/docs/ldv/salesforce_large_data_volumes_bp.pdf
CLKCLK
If you have record types on opportunity then divide the queries per record type. this way you will have two index fields in where clause plus recordtypeid will be standard indexed field.