Tell me more ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.
up vote 0 down vote favorite

Does including a where clause that refers to a field on a related object in a query mean that the query is selective?

An example: We have Object A and Object B. Object B has a field that is a lookup to Object A. Object A has a field called External_ID__c that was created as an External ID.

In Apex we have something along the lines of:

Set<String> strings = new Set<String>();
// strings gets populated with some values

List<ObjectB> objectBs = [select ID from ObjectB where ObjectA__r.External_ID__c in :strings]

I don't have the means to test this at the moment, so war stories are welcome!

share|improve this question

2 Answers

up vote 2 down vote accepted

A query is not selective simply because it includes a where clause.

Per the SalesForce docs on the topic, Working with Very Large SOQL Queries specifically the section 'More Efficient SOQL Queries', a query is selective when it meets these criteria:

  • A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.
  • The selectivity threshold is 10% of the records for the first million records and less than 5% of the records after the first million records, up to a maximum of 333,000 records. In some circumstances, for example with a query filter that is an indexed standard field, the threshold may be higher. Also, the selectivity threshold is subject to change.

Because your query is on a field which is marked as an ExternalId, and thus indexed, it will be used in determining if the query meets the selectivity threshold. It is possible that your query could return a great enough number of rows to become non-selective. (Thresholds for this scenario are in the linked article as well.)

Be sure to note the items in the 'Custom Index Considerations for Selective SOQL Queries' section of the linked article.

share|improve this answer
Thanks Mark. I knew that a where clause was not sufficient, but reading what you quoted, it would appear the where clause criteria in my example would be a selective query. Accepting as the answer, but welcome any other relevant experiences! – GBreavin Jan 16 at 16:48
up vote 0 down vote

yes, adding a where clause in a SOQL query will make that query a 'Selective Query'. A query is selective when results are returned using a filter which limits the total number of records that could be returned.

For more check this link: http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

share|improve this answer
Thanks for the quick response. Simply adding a clause won't make it selective - you need a clause that uses an indexed field. What I'm struggling to confirm is whether a clause that refers to an indexed field on a object related to the one being queried is enough to avoid "Non-selective query" errors. – GBreavin Jan 16 at 16:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.