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
ChickenOrBeefChickenOrBeef 

Error: Non-selective query against large object type

Hello everyone,

When our users edit a task, they receive this error:


MainTriggerTask: execution of AfterUpdate

caused by: System.QueryException: 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)

Class.ClassTaskUpdateRelatedActivity.updateRelatedActivity: line 30, column 1
Trigger.MainTriggerTask: line 30, column 1



Here is the trigger that's causing the issue:

public class ClassTaskUpdateRelatedActivity{
    
    public void updateRelatedActivity(List<Task> tasks, Map<ID,Task> oldTasks){
        
        Set<String> tasksInTrigger = new Set<String>();
        Map<String,String> ownerMap = new Map<String,String>();
        Map<String,String> descriptionMap = new Map<String,String>();
        Map<String,String> subjectMap = new Map<String,String>();
        Map<String,Date> dateMap = new Map<String,Date>();
        List<Related_Activity__c> relatedActivitiesToUpdate = new List<Related_Activity__c>();
        
        FOR(Task t : tasks){
            IF(t.OwnerId != oldTasks.get(t.Id).OwnerId || t.Description != oldTasks.get(t.Id).Description || t.Subject != oldTasks.get(t.Id).Subject
               || t.ActivityDate != oldTasks.get(t.Id).ActivityDate){
                
                tasksInTrigger.add(t.Id);
                ownerMap.put(t.Id,t.OwnerId);
                descriptionMap.put(t.Id,t.Description);
                subjectMap.put(t.Id,t.Subject);
                dateMap.put(t.Id,t.ActivityDate);
                
            }
        }
        
        IF(tasksInTrigger.size() > 0){
            FOR(Related_Activity__c ra : [SELECT
                                            Id,Assigned_To__c,Activity_ID__c,Description__c,Name,Date__c
                                          FROM
                                            Related_Activity__c
                                          WHERE
                                            Activity_ID__c In :tasksInTrigger]){
                                                
                                                IF(subjectMap.get(ra.Activity_ID__c).length() > 80){
                                                    ra.Name = subjectMap.get(ra.Activity_ID__c).SubString(0,80);
                                                }
                                                ELSE{
                                                    ra.Name = subjectMap.get(ra.Activity_ID__c);
                                                }
                                              
                                              ra.Assigned_To__c = ownerMap.get(ra.Activity_ID__c);
                                              ra.Description__c = descriptionMap.get(ra.Activity_ID__c);
                                              ra.Date__c = dateMap.get(ra.Activity_ID__c);
                                                
                                              relatedActivitiesToUpdate.add(ra);
                                              
                                          }
        }
        
        IF(relatedActivitiesToUpdate.size() > 0){
            
            UPDATE relatedActivitiesToUpdate;
            
        }
        
    }

}

Some background on what's going on here:

We have a custom object called "Related Activity", which is basically a copy of a task. It's attached to all the accounts related to that respective task's account. So for example, if you create a task on an account that has a parent account and two sibling accounts, then a Related Activity will be created for the parent and two siblings. It's a copy of that task.

The trigger posted above simply updates the related activites when a task has been updated. But as you can see, when our users try editing a task, they receive the error above.

Any idea why that error would occur? One thing I should mention is that there may not be any Related Activities for a given task, since the account may not have any related accounts. I'm not sure if that would cause an issue.

Let me know if you need any other info from me!

Thanks!
-Greg
Best Answer chosen by ChickenOrBeef
ChickenOrBeefChickenOrBeef
Hey pcon,

Just to update on this, I had to get Salesforce support to make Activity_ID__c an indexed field. That did the trick.

Thanks for your help!
-Greg

All Answers

pconpcon
If you put a debug statment before you loop and inspect the tasksInTrigger variable, how many ids are stored in that variable?  Can you then run that in something like workbench with a count to see how many Related_Activity__c return?
ChickenOrBeefChickenOrBeef
Hey pcon,

1) So should the debug statement go in line 24, between the two FOR loops? Should I loop through every ID in "tasksInTrigger", or should I just print out tasksInTrigger[0]? When our users were editing the task, it was only one task they were editing. So I'm guessing I don't need to loop through it?

2) When our users experienced the error, there were no Related Activities, since the accounts didn't have any related accounts. So when the trigger got to the SOQL query, there were no Related Activities to find. Is that the issue?

Thanks!
-Greg
pconpcon
I would put it where you said, on line 24.  My hypothosis is that the set of ids has a null in it and that is causing it to leak through the size check (btw isEmpty() is better to use) and causing your SOQL to try to query for any with a null Activity_Id__c
ChickenOrBeefChickenOrBeef
I added the following two debug statements (lines 26 and 53):

public class ClassTaskUpdateRelatedActivity{
    
    public void updateRelatedActivity(List<Task> tasks, Map<ID,Task> oldTasks){
        
        Set<String> tasksInTrigger = new Set<String>();
        Map<String,String> ownerMap = new Map<String,String>();
        Map<String,String> descriptionMap = new Map<String,String>();
        Map<String,String> subjectMap = new Map<String,String>();
        Map<String,Date> dateMap = new Map<String,Date>();
        List<Related_Activity__c> relatedActivitiesToUpdate = new List<Related_Activity__c>();
        
        FOR(Task t : tasks){
            IF(t.OwnerId != oldTasks.get(t.Id).OwnerId || t.Description != oldTasks.get(t.Id).Description || t.Subject != oldTasks.get(t.Id).Subject
               || t.ActivityDate != oldTasks.get(t.Id).ActivityDate){
                
                tasksInTrigger.add(t.Id);
                ownerMap.put(t.Id,t.OwnerId);
                descriptionMap.put(t.Id,t.Description);
                subjectMap.put(t.Id,t.Subject);
                dateMap.put(t.Id,t.ActivityDate);
                
            }
        }
        
        FOR(String tid : tasksInTrigger){
            system.debug('Task ID is ' + tid);
        }
        
        IF(!tasksInTrigger.isEmpty()){
            FOR(Related_Activity__c ra : [SELECT
                                            Id,Assigned_To__c,Activity_ID__c,Description__c,Name,Date__c
                                          FROM
                                            Related_Activity__c
                                          WHERE
                                            Activity_ID__c In :tasksInTrigger]){
                                                
                                                IF(subjectMap.get(ra.Activity_ID__c).length() > 80){
                                                    ra.Name = subjectMap.get(ra.Activity_ID__c).SubString(0,80);
                                                }
                                                ELSE{
                                                    ra.Name = subjectMap.get(ra.Activity_ID__c);
                                                }
                                              
                                              ra.Assigned_To__c = ownerMap.get(ra.Activity_ID__c);
                                              ra.Description__c = descriptionMap.get(ra.Activity_ID__c);
                                              ra.Date__c = dateMap.get(ra.Activity_ID__c);
                                                
                                              relatedActivitiesToUpdate.add(ra);
                                              
                                          }
        }
        
        system.debug('Related Activities size is ' + relatedActivitiesToUpdate.size());
        
        IF(!relatedActivitiesToUpdate.isEmpty()){
            
            UPDATE relatedActivitiesToUpdate;
            
        }
        
    }

}

Then, in the Sandbox, I updated a task that had no Related Activities and these were the debug statements:

User-added image


Then, still in the Sandbox,  I updated a task that had three Related Activities and these were the debug statements:

User-added image


Both edits worked in the Sandbox, but that's probably because there are only a handful of Related Activities in the Sandbox, while there's over 100,000 Related Activities in Production.


Any thoughts on next steps? Thanks so much for your help so far!
pconpcon
The only thing I would recommend is that on line 28 you add a tasksInTrigger.remove(null) just in case (no clue how) the set gets a null in there and that is what is going to your query.
ChickenOrBeefChickenOrBeef
OK, so just to clarify, is there any other reason why this error might be occuring besides the possibility of a NULL value being in the 'tasksInTrigger' set?

And if that is the only possible reason, then I'm assuming your recommendation would solve it?
pconpcon
Correct.  That's my best guess.  Unless you've got A BUNCH of Related_Activity__c with a single Activity_Id__c that your users happen to be hitting.  It might be worth using something to do some counts in your production instance grouped by Activity_id__c.  My guess is you'll see a bunch of them with null.  If that's the case, then removing the null from the set will fix it.
ChickenOrBeefChickenOrBeef
I just checked to see how many Related Activities have a NULL Activity ID, and there were none. They all had an Activity ID.

Does that change things? Or could a NULL value in 'tasksInTrigger' still be the issue?
pconpcon
That does change things... it's really odd that you're getting this.  The next thing I would suggest looking to see if you happen to have a Activity_Id__c that is way higher than any other

select Activity_Id__c, count(Activity__Id__c)
from Related_Activity__c
group by Activity_Id__c
order by count(Activity_Id__c) desc
limit 10

and see if you have any that's got weird data.  If nothing stands out, I'd say push the debug code to your prod sandbox and monitor a user while they make it fail.  See if you get any better data out of it then.
ChickenOrBeefChickenOrBeef
Hey pcon,

Sorry for the delayed response ( a few other things came up), but where should I make that query? 
pconpcon
I would run that in workbench or the developer console.  It's just to gather more data to see if you have any outliers in your Related_Activity__c data.
ChickenOrBeefChickenOrBeef
Hey pcon, I'm back at this again!

Anywho, I tried making that query in Workbench, but I received the following error. Let me know what I'm doing wrong:


User-added image
ChickenOrBeefChickenOrBeef
Never mind. I just removed the "count(Activity_Id__c)" and it worked.

Here are the results. It looks like none of them have a blank Activity ID.

User-added image



I then did a simple query for any Related Activity that had a NULL Activity ID, and there were none:

User-added image


Thoughts on next steps?
pconpcon
that's my fault :/  I gave you a bad query.  Try this:

select Activity_Id__c, count(Id)
from Related_Activity__c
group by Activity_Id__c
order by count(Id) desc
limit 10

that should count the number of related_activity__c objects grouped by activity_id__c
ChickenOrBeefChickenOrBeef
Thanks Pcon. Here are the results with the count. It looks like the highest is only 364.


User-added image
ChickenOrBeefChickenOrBeef
Hey pcon,

There's one other class that's giving me the "Non-selective query" error as well:

public class ClassRelatedAccountAfterDelete{
    
    public void updateRelatedTasks(List<Account> accounts){
        
        List<Related_Activity__c> relatedActivitiesToDelete = [SELECT
                                     							Id
                                      						   FROM
                                     							Related_Activity__c
                                      						   WHERE
                                      							Related_To__c = NULL];
        
        IF(relatedActivitiesToDelete.size() > 0){
            DELETE relatedActivitiesToDelete;
        }
        
    }

}

The Related Activity object has an Account look-up field called "Related To", so if an Account gets deleted, that "Related To" field will turn blank if the deleted Account was in that field. So this trigger simply runs after an Account gets deleted, it queries all Related Activities with a NULL "Related To" value, and it deletes them.

When I tried merging two Accounts, I received the "Non-selective query" error. Is this easier to figure out? Could it be related?

Thanks!
-Greg
ChickenOrBeefChickenOrBeef
Hey pcon,

After doing a bit more research on this, it turns out that a query can be "non-selective" if it's querying an object that has over 100,000 total records, even if the query itself isn't returning 100,000 records.

There are indeed over 100,000 Related Activities, so from what I understand I have to simply include more filters or more indexed fields. The problem is that I'm not sure which filters/indexes or how many filters/indexes I need to make the query selective.

Do you have any thoughts on what filters or indexes I should use?

Thanks!
-Greg
pconpcon

I'm suprised that it is complaining about that for your query.  I would think that your Activity_Id__c (being a lookup relationship) would be a selective query.  It may only consider it selective if it's a M/D relationship instead of a lookup.  

The only way you can make this work (assuming Related_To__c is also a lookup) is to change it to a Master/Detail.

PS: Sorry for the delay, it's been a crazy week.
ChickenOrBeefChickenOrBeef
Hey pcon,

I think the confusion here is that Activity_ID__c is NOT a lookup field. It's just a text field. So it's not indexed. Do I just need to ask Salesforce support to make "Activity_ID__c" indexed? Or perhaps I should just change it to a lookup field?

And no worries about the response time! Thanks for all your help so far.
pconpcon
Ah! Well, that will do it.  If it is indeed a lookup to an activity it make sense to make it a lookup.  The only problem will be that you can't just convert it, so you'll probably have to add the new field, change your logic to make sure that gets populated then data loader all of the old values into your new fields.

As for having SFDC index it, I'm not sure if they can do that.
ChickenOrBeefChickenOrBeef
Hey pcon,

Just to update on this, I had to get Salesforce support to make Activity_ID__c an indexed field. That did the trick.

Thanks for your help!
-Greg
This was selected as the best answer