2

I have a bit of code in production that was working for about 3 days, then started failing with the old 'Non-selective query against large object type (more than 200000 rows)'. Here's the query:

SELECT id, RecordTypeId, tasksubtype, WhoId, WhatId, NPS_Email__c, CreatedDate, Owner.FirstName
FROM task
WHERE (NPS_Email__c IN: contactEmails OR WhatId IN: npsCaseMap.keySet())
AND (tasksubtype='email' OR tasksubtype='call' OR RecordTypeId=:smsTaskRTId OR RecordTypeId=:npsTaskRTId) 
AND CreatedDate>=:taskDateScope ORDER BY CreatedDate DESC

This query should be pretty specific and only return roughly 1-20 rows on average - it's triggered off closure of cases and is to count other tasks logged against the contact the case relates to within a specific date range. However we have a lot of task records in our system - almost 28 million. If it was an issue with number of records on the object it should have thrown this error right from the start, but as I mentioned this was working for 3 days before we started getting the error. Can you get this error through just sheer number of records on an object? I've read a query filter that can return a null could be an issue - with my query would this only potentially be in the lists referred to in my query?

3

You are right that collections containing null might hurt your selectivity (see Improving Performance by Not Searching on Null Values). So one step would be to make sure npsCaseMap.keySet() does not contain a null key.

Take a look at the Query & Search Optimization Cheat Sheet. Another thing you do in your query which makes it harder to be selective is use OR all over the place. Including more AND clauses can make it more difficult as well, but OR is much worse. At least some of your OR clauses are easy to remove, use IN instead when filtering on multiple values for the same field:

Instead of:

WHERE MyField__c = 'A' OR MyField__c = 'B'

Use:

WHERE MyField__c IN ('A', 'B')

You can apply this change to both your TaskSubType filters and your RecordTypeId filters.

However, even making the above recommended changes is not guaranteed to make your query selective. You still have a lot of clauses you are trying to combine. It might be worth trying to split the query out into a few distinct queries which have simpler filter logic, eliminating OR clauses if possible, and reducing the number of clauses overall.

1

Given the number of Tasks you have, and that your query was working, your issue might be one of index skew.

I still think that I don't quite grasp the definition of index skew, and my org isn't large enough to regularly run into such issues, but my understanding is this:

  • Index skew is when the value of an indexed field (like relationship fields such as AccountId on Opportunity) contains the same value on many more records than average. For example, in my org, a typical Account may have around 10 Asset records related to it. There are a few Accounts which have between 10,000 and 90,000 Asset records. Those exceptional Accounts 'skew' the index
  • Index skew can make it so that there isn't much benefit in Salesforce trying to optimize your query using the skewed index. If using that index results in the least costly query, Salesforce will still use it. However, if the computed 'cost' of that query is still over 1.0, your query won't be selective.
  • Index skew might only be a problem when your query includes a skewed value. For example, if I query Asset [SELECT Id FROM Asset WHERE AccountId IN :acctsList], and acctsList contains the Id of one (or more) of my exceptional Accounts, I'm liable to get a selectivity exception
  • Index skew may cause query selectivity issues even below the 200,000 record threshold mentioned by the error message

The fields with indices (that I know about) in your query are WhatId and RecordTypeId.

With 28 million records, RecordTypeId probably isn't too useful as an index, even if it isn't skewed, unless you have a ton of them (the absolute maximum number of rows a query could potentially return and still be selective is 1 million. If the distribution of record types were perfectly uniform, you'd need 28 or 29 separate record types to hit that target).

My guess is that npsCaseMap contains an Id for a record that has an abnormal number of Tasks. To figure out if that's the case, you might be able to run something similar to this query (via the query editor in the developer console, which appears to be more tolerant of large and non-selective queries)

SELECT WhatId, COUNT(Id) 
FROM Task 
GROUP BY WhatId 
HAVING COUNT(Id) > 1000 
ORDER BY COUNT(Id) DESC

Increase/Decrease the 1000 in the HAVING clause as appropriate to narrow down your culprit(s).

As for working around the issue, if it's a 'once in a blue moon' type event, you may just want to handle things manually. Use the dev console to run the query, update the count yourself, then include some logic to only query Tasks for Cases that don't have their count set.

I don't really have an idea about how you might try to handle things automatically. If it is an index skew issue, removing one record from your query may fix things. Then again, it could be a combination of skewed index values causing you trouble. In both cases, the issue is that we don't really have the tools to determine which record(s) is/are causing us problems.

About the best advice I can give here is to echo something that Adrian said. Be sure to remove nulls from variables you're binding into your query (or prevent nulls from getting into them in the first place). In my org, there are 6600+ Tasks with no WhatId. The next most referenced WhatId value is referenced by 322 Tasks. It could very well be that a null value in npsCaseMap.keySet() is causing your issues.

0

Thanks for the advice all. In the end what resolved this immediately was a call to Salesforce support to clear deleted records from the server - we had several million deleted tasks that were hard deleted but still sat on the server, causing the table to be much larger than it should have been. We also asked for the NPS_Email__c field to be indexed and optimised the query along the lines of Adrian Larson's suggestions to avoid non-null queries; since then everything's been running well.

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.