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
CKNCKN 

SystemQueryException. Non Selective Query against large object type

Hi,

 

We are getting the following error when trying to deploy a trigger.

 

 

ACCFTriggerValidation.myTest()Class773Failure Message: "System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, tgrBatchJobs: execution of BeforeInsert caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com ...

 

The code snippet is attached. We are not sure why the batch upload fails and how we should index our fields. We are new to APEX so not sure how to get started.

 

// code snippet

//BATCH VALIDATION
      PreferredContact__c mycontact = new PreferredContact__c(Account__c=accounttest.id, preferredemail__C='user@site.com', preferredfax__c='3345567890');
      insert mycontact;
        
    PreferredIA__c testme = new PreferredIA__c(OKSendSurvey__c = true, account__c = accounttest.id, specialist__c = userid[0].id);
    insert testme;
    
    Batch_Job__c[] jobme = new batch_job__c[]{new Batch_Job__c(IsRunning__c = true, Description__c = 'test neutral', JobType__c = 'Test Email', DeleteMe__c = false),
                                           new Batch_Job__c(isRunning__c = true, Description__c = 'test positive', jobType__c = 'Send Surveys', DeleteMe__c = false),
                                            new Batch_Job__c(isRunning__c = true, Description__c = 'test positive', jobType__c = 'Send Surveys', DeleteMe__c = false)};
    insert jobme;
    
    jobme[0].Deleteme__c = true;

// error on line 77 column 3

    update jobme[0];
    
    jobme[1].Deleteme__c = true;
    update jobme[1];

paul-lmipaul-lmi

non-selective means you have a query somewhere that needs a WHERE clause, and even more so, the where clause should be on a field that is indexed.

 

example

 

Contact[] con = [select id from contact limit 200]

this one will fail if the Contact object has more than 100000 rows, even with the limit statement, because I have no where clause

 

better

Contact[] con = select id, email from contact where email = 'test@test.com' LIMIT 200];

this is OK because I have a where clause, and also am querying on an indexed field (email)

 
paul-lmipaul-lmi

additionally, I believe SF has documentation somewhere on what fields are indexed by default.  custom fields are not indexed unless you check the "Unique Identifier for External System" checkbox when creating the field. 
CKNCKN

Thanks so much.. I will read the documentation and test this out.

Chitra
gv007gv007

Whenever you are wrting some some database operation write the queryies and db operations in try catch balck and fix the bug in query.
CKNCKN

Thanks Gopi ..
AlexPHPAlexPHP

We also ran into this issue TODAY infact.

 

We had no idea such a critical mass existed.  Apparently this only affects objects which have 100,000 records or more.  So it was like a ticking time bomb that we didn't know about.  :(

 

We resolved our issue (for the time being) by changing one of our queries in a trigger corresponding to the affected object.
CKNCKN

same issue here...:)
CKNCKN

Alex,

I was not able to resolve the issue. Is it possible for you to share the mods you made to your query? Might give me some pointers.

Thanks

Chitra
AlexPHPAlexPHP

I just came in Monday morning and the issue resurfaced for me too! :(  SalesForce support has NOT responded back to us to setup any custom indexes!!!

 

I thought my modification of the query worked, but apparently there are still some cases where it is returning too many records.

 

Going to have to break up my query into multiple queries....
CKNCKN

SalesForce has not responded to us either. We are having issues with Acc name field in Account object.  Out of the box it is not a an indexed field and we have put in a request with SalesForce.

 
AlexPHPAlexPHP

Do you happen to know how we can tell which fields are indexed and which are not?  Is there an indicator somewhere?

 

Also, the exception message about the non-selective query states that "Even if a field is indexed a filter might still not be selective..."

 

That is why I am trying to break up my query into multiple queries.

 

It seems like indexing a field would only help with performance, but not with data skew issues.
CKNCKN

I found this in the documentation. Might be useful.. Check out the section where it talks abt index

 

http://www.salesforce.com/us/developer/docs/api_meta/Content/customfield.htm

 

Chitra
AlexPHPAlexPHP

Ah, yes, I looked at that before too.  But not sure where to get all the information listed in that doc.
AlexPHPAlexPHP

On another note, the main reason we are constructing these large queries to fetch as much information as possible in a single query is to avoid running into other governor limits (number of SOQL queries).

 

We were running into a big slew of "Too many SOQL queries" exceptions before.  The maximum allowed in a single data flow (transaction) is 20.

 

So by breaking up queries into smaller ones, we try to avoid this non-selective query issue, but it may re-open the "too many SOQL queries" can of worms!
CKNCKN

Alex,

 

For a given object , all look up fields, external ID fields and ID fields are indexed. Got this info from the specialist who is working wth us on the issue.

 

We are still trying to resolve it. Hopefully will get it fixed today.

 

Chitra

 
AlexPHPAlexPHP

Thanks for the info.

 

The query we're dealing with here is filtering on all indexed fields.  Then for us it must be a data skew issue.  Not sure what can be done about that.

 

We will call SalesForce to escalate the issue.

 

Keep us updated on your progress.  Thanks.
AlexPHPAlexPHP

We finally got a response from SalesForce support on our issue.

 

Basically, there's not much else that we can do besides what we already did.

 

That is, we broke up a query in to a couple smaller queries.  By doing so, the number of records selected for each would not break their threshold to label the query as being non-selective.

 

Since the fields we are filtering against are foreign keys (to other objects) and the ID of the object itself, there's no further custom indexing that they can do for us.

 

Lastly, this certainly doesn't help our issue of hitting the Too many SOQL queries governor limits, because now we put in another query. :(

 

If anyone has other information or advice on this topic, please share it.  It is appreciated.

 

Thanks
paul-lmipaul-lmi

are you able to do this logic as part of batch apex?  that's another option with large data sets, from what I understand.
CKNCKN

Our  query was written as a batch process but we still ran into the issue. Lucklily for us we did not have any extercnal fields so SalesForce was able to make one of our foreign keys as an external id. That has worked for us
AlexPHPAlexPHP

Unfortunately, I am not able to use Batch Apex for my implementation because we need real-time updates of related objects through the use of triggers.

 

Batch Apex and/or using the @future annotation would not be able to provide the functionality needed to carry out our business logic.