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

on

Debugging the error message: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.

Also known as - too many rows in your database table and no index fields

During a recent project I kept seeing the error below in the Salesforce Debug log. This was a new error to me so it took a long time to work out why my code was failing. 
System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.
Having found the cause of the issue I wanted to write it up to hopefully save anyone else facing this problem some time.


The SOQL query throwing the error

After turning up debug monitoring to max and running a few scenarios I traced the cause of the error down to the piece of code below which was querying the Task Object.
list<Task> tList = [SELECT CallObject, Id, WhoId, WhatId, NVM_Agent__c, CallDisposition FROM Task t WHERE t.CallObject != null AND NVM_Agent__c != null AND t.CallObject in :callGuidSet];
I was surprised that this piece of code was causing errors as I'd used it several times before without issue. After a lot of digging around I came upon a few forums talking about queries failing when there is over 100,000 records in a table.

"When using a field in your "where" clause of a query, it's best to try to have that field indexed by checking the "external ID" checkbox when you define that field.  (Note: for every object, only a limited number of fields can be indexed: see the online help for the limit.)" (Developer Force)

So I decided to check how many Task records this customer had in their production ORG.

Finding out how many rows you have in a data table

  1. Log in to Salesforce
  2. Navigate to Company Overview
  3. Click on Data Storage
  4. Find the Table that you are querying in the list 
  5. Check whether it has over 100,000 rows in it - if it does, read on :)

System Overview page screenshot

Storage Usage page screen shot

Surely you can keep more than 100,000 items in a table?

Absolutely! 

However, if you are querying against a table with more than 100,000 Salesforce is going to be stricter on your Query to ensure that it completes within the designated time. 

That's the first part of the error: "Non-selective query against large object type (more than 100000 rows)".

To remove the error you can do a couple of things - either simplify the query or request that a new index field is added to the object you are querying to speed up the search. 

That's the 2nd part of the error: "Consider an indexed filter or contact salesforce.com about custom indexing."

Raising a Salesforce Support case to add a new index

In my case adding a new field index seemed like the best way to go and it was refreshingly easy to do. Just raise a ticket with Salesforce support and ask them to designate the field you are querying as an external ID and add an index. 

Here's a copy of my Salesforce support ticket:
Hi Salesforce Support
 Please can you add an index to the 'CallObjectId' field on our task object. Please can you add this index in our Production as soon as possible.
Salesforce added the index the next day and the error message disappeared - Yeah!

What fields are indexed to start with?

The platform automatically maintains indexes on the following fields for most objects.
  • RecordTypeId
  • Division
  • CreatedDate
  • Systemmodstamp (LastModifiedDate)
  • Name
  • Email (for contacts and leads)
  • Foreign key relationships (lookups and master-detail)
  • The unique Salesforce record ID, which is the primary key for each object
  • Salesforce also supports custom indexes on custom fields, with the exception of multi-select picklists, text area (long), text area (rich), non-deterministic formula fields, and encrypted text fields.
External IDs cause an index to be created on that field, which is then considered by the Force.com query optimizer. External IDs can be created on only the following fields.
  • Auto Number
  • Email
  • Number
  • Text
  • To create custom indexes for other field types, including standard fields, contact salesforce.com Customer Support

Further reading

There's a lot more detail about this issue on Developer Force. Here are a few of the posts that helped me solve this issue:

http://salesforce.stackexchange.com/questions/33860/is-accountid-a-standard-index-on-task-table

Comments

Post a Comment