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
BerettaJonBerettaJon 

Trigger error during query - Non-selective query against large object type (more than 100000 rows

Hi internet,

 

I have a pretty simple query in a User Trigger that is supposed to determine all records in a custom object that are owned by the Users being updated in the Trigger.

 

 [select CreatedByID from CustomObject__c where CreatedByID in :Trigger.newMap.keyset()]

 

CustomObject__c has over 140,000 rows.  Trigger.newMap.keyset().size = 1.

 

Interestingly I am able to manually run this query through the developer console and it returns 0 rows (rather quickly too...)

 

What is the correct way to query a large table?  

 

Note I am aware that I cannot return more than 50,000 rows in a query but I do not expect this query to return more than a couple dozen ever.

 

Full error:

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

 

 

EDIT

Im doing some more research and found this

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

 

  • The following fields are indexed by default: primary keys (Id, Name and Owner fields), foreign keys (lookup or master-detail relationship fields), audit dates (such as LastModifiedDate), and custom fields marked as External ID or Unique.

The CreatedByID is the owner field of my custom object so according to SF docs my query should be fine??

Best Answer chosen by Admin (Salesforce Developers) 
JWykelJWykel
I did some quick tests and found that OwnerId was not correct, but the object I tested on was a master/detail with something else, which might keep that field off the 'detail' and only on the 'master'. Is your object in a master/detail relationship?

All Answers

magicforce9magicforce9

Hi,

 

May be try  [select CreatedByID from CustomObject__c where CreatedByID in :Trigger.newMap.keyset() LIMIT 50000] or any lower number.

 

 
BerettaJonBerettaJon

That would no longer meet the need of the query, what happens if the record I want is above the 50,000 limit? And again this query will not return more than 1000 records ever.  The 50k limit on returned results is not the problem.

 

Why doesn't the createdbyid field in the where clause count as a selective filter?  Ownership fields are indexed, no?
JWykelJWykel
CreatedByID is not the owner field! CreatedBy is who created the record. Owner is who owns the record, they are two different things.

That being said, what does the rest of the trigger look like? Particularly, are you limiting the trigger that runs your example code to only be insert/update? If it's running while deleting, Trigger.newMap will be null and may push you into the #1 example the error states, filtering on null.
BerettaJonBerettaJon

Good point about CreatedByID.  Forgive however as I cannot find the ownership field on my custom object.  What is the normal ownership field for custom objects?  Owner__r.id - ownerID - owner.id  dont work

 

The trigger could run during delete but this error is happening during an update.  The error is definitly happening at the query posted above.
JWykelJWykel
I did some quick tests and found that OwnerId was not correct, but the object I tested on was a master/detail with something else, which might keep that field off the 'detail' and only on the 'master'. Is your object in a master/detail relationship?
This was selected as the best answer
BerettaJonBerettaJon

Once I used the correct owner field (Account__r.ownerID master detail relationships always confuse me) the query runs fine since it is now using a selective indexed owner field to filter.

 

Thanks internet.