3

We have following query in our code and its giving timeout error.

Select email, firstname, lastname, id 
From contact 
Where Email != Null 
And Opted_Out__c = false 
And id IN (Select contactid From CampaignMember Where campaignId = 'XXXXXXXXXXXX') 

When we contacted SFDC support they said the query is not selective.

In SFDC documentation its mentioned that :

If the filter is on a standard field, it will have an index if it is a primary key (Id, Name, OwnerId), a foreign key (CreatedById, LastModifiedById, lookup, master-detail relationship), an audit field (CreatedDate, LastActivityDate, SystemModstamp).

In above example both inner and outer SOQL uses ID and so it should be selective query.

Can someone takeout time and explain me why its not Selective Query?

3

I think i found the reason why its not selective query.

When we use inner query SFDC cant use index. It doesnt matter whether we pass NULL into IN clause. If we just pass the list of IDS then query becomes selective and it works fine.

Here is is screen shot of my test.enter image description here

This blog helped me to reach to this conclusion.

2

If you were already in touch with SF support, it doesn't harm to ask them for more details, they have access to data & logs specific to your org. I believe the culprit is the following:

Where Email != Null

As far as I know the != operator can not rely on idexes and will always result in a full table scan.

The documentation states:

Typically, a custom index won't be used in these cases:

  • The filter operator is a negative operator such as NOT EQUAL TO (or !=),NOT CONTAINS, and NOT STARTS WITH The CONTAINS operator is used in the filter and the number of rows to be scanned exceeds 333,333. This is because the CONTAINS operator requires a full scan of the index. Note that this threshold is subject to change. When comparing with an empty value (Name != '')

The Query & Search Optimalization Cheat Sheet also lists != as an exception.

  • Hi Samuel, Thank you very much for your reply on this. I don't think that Email != Null can make query non selective. As per documentation any query with at least one one selective term is called as selective query. Please refer example # 5 – AtulRajguru9 Jan 19 '15 at 11:51
  • Same question is sent to SFDC support too so lets see who can solve this fast :) – AtulRajguru9 Jan 19 '15 at 11:53
  • Please refer example # 5 from this link help.salesforce.com/apex/… – AtulRajguru9 Jan 19 '15 at 12:10
  • In my understanding it's only selective when it returns below a certain % of your data, regardless of how many filters and indexes that requires. I don't see how #5 explains anything, many presentations and webinars around this subject mention that using the != operator is a bad idea for, as I believe, the reason as described above. But yes, maybe I misinterpreted that, i guess we'll see how the community evaluates my answer. – Samuel De Rycke Jan 19 '15 at 12:25
  • I certainly agree that != is bad idea but i doubt that its creating problem in this case. I think Null is root cause here. I am running Explain and will share result with you. Thank you very much for your help on this! :) – AtulRajguru9 Jan 19 '15 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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