+ Start a Discussion
Bhawani SharmaBhawani Sharma 

Non-selective query against large object type

I am facing this issue these day and I also got the idea from community to solve it, but still I am not very much clear with the solution.


My scenerio is :

List<Lead> listLead = [Select Id from lead where Name IN : setNames];


and I am getting exception :

"System.QueryException: Non-selective query against large object type".

 

Now the solution says :

1. it's best to try to have that field indexed by checking the "external ID" checkbox when you define that field.

 Question:  In my scenerio we are quering the data using the native Name field, Can this field be marked as "External Id" ?

 

2filter out nulls in your Apex query. 

Question: Name field can not be null for lead, then how this statement can be meaningful? 

Please suggest.


Regards,

Bhawani

minkeshminkesh

Hello,

           can you post your full code so that i can understand? try to add Name field which you are using to compare it with setName.

 

Thank you ,

Minkesh patel

Bhawani SharmaBhawani Sharma

Hi Minkesh,

 

Thanks for replying. In trigger I have only query :

List<Lead> listLead = [Select Id from lead where Name IN : setNames];

 

nothing else.Trigger's other part is not related to query.

 

setNames is a set of string.

 


minkeshminkesh

Hello,

           Strange. it must work. did you try to run query in eclipse or force.com ?

Thank you,

Minkesh Patel

Bhawani SharmaBhawani Sharma

Hi Minkesh,

 

Actually it's not very strange, because from trigger if querying on a large object having 100k records then salessforce doesn't allow querying withiout any indexing and this exception comes.

 

 

paul-lmipaul-lmi

right, but, the Name field IS indexed on standard objects.  otherwise, you couldn't search for that object by name...

Bhawani SharmaBhawani Sharma

Hi Paul-Imi

 

Thanks for reply, but I really couldn't understand line

"you couldn't search for that object by name".

 

Any field can be used to querying the records from the database wheather it is indexed or not.

 

Can you please explorer this?

 

 

bob_buzzardbob_buzzard

Name is an indexed field according to the apex developer's guide.

 

Do you have a null in your set of names?  That could cause problems for a query of this nature.

 

Also, according to another thread, if the query would return more than 10% of the contents of the table it is considered non-selective.

Bhawani SharmaBhawani Sharma

Thanks Bob,

 

You mean salesforce will never allow me to query the records using the NULL criteria? I mean if null is in set of value.

 

List<Lead> listLead = [Select Id from lead where Name IN : setNames];

 

What is the workaround if I need to read the entire database having more than 100k records and  matches are more than 10k ?

 

 

Bhawani SharmaBhawani Sharma

One more doubt Bob, this limitation is only with the trigger or same case is for Apex Class, System log, Apex-API call etc..

bob_buzzardbob_buzzard

Salesforce will allow you to query records using a null criteria, but if you do this on a large set of data, even if the field is indexed, the query becomes non-selective (presumably null matches any value).

 

Looking at the various threads on this, it looks like you'll need to contact support regarding this.

Bhawani SharmaBhawani Sharma

Okay,  that means no work around for this apart for salesforce support.

 

Is this limitation is for ApexClass, System log also ?

bob_buzzardbob_buzzard
No workaround that I am aware of. It will apply wherever the query is executed from as it is a database exception.
samdsamd

I know this thread is a bit old, but I hit the same error and found in my investigations that the selective query limit is enforced differently inside triggers as opposed to elsewhere.  We had a query that would run perfectly under "Execute Anonymous" in the system log viewer, but would fail when run inside a trigger. Pg 218 of the Apex Developers Guide Spring '12 pdf reads "For best performance, SOQL queries must be selective, particularly for queries inside of triggers. To avoid long execution times, non-selective SOQL queries may be terminated by the system Developers will receive an error message when a non-selective query in a trigger executes against an object that contains more than 100,000 records"

 

This doesn't help to solve the issue, just thought I'd add my finding since it took me a while to figure out!

 

Sam