+ Start a Discussion
justin_sfdcjustin_sfdc 

System.QueryException: Non-selective query against large object type (more than 100000 rows).

Hi,
I have this error: 
execution of AfterInsert 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):Trigger.TerritoryAccountTeamMemberUpdate: line 62, column 1
It is showing me an error in the query that I am using in the Map.
Below is the code;

trigger TerritoryAccountTeamMemberUpdate on TerritoryAccountTeamMember__c (after insert, after update, after delete) {

     set<Id> terrMembers= new set<Id>();
    //execution of after Insert or update trigger
    if (trigger.isInsert || trigger.isupdate) {
        for (TerritoryAccountTeamMember__c territoryMembers: trigger.new) {
            System.debug('-=TerritoryMembers in the list are: ' + territoryMembers);
            terrMembers.add(territoryMembers.territory__c);
        }
    }
    //execution of after delete trigger
    else if (trigger.isDelete) {
        //Loop through the list of TerritoryAccountTeamMember__c that was deleted
        for (TerritoryAccountTeamMember__c territoryMembers: trigger.old) {
            System.debug('-=-TerritoryMembers in the list are: ' + territoryMembers);           
            terrMembers.add(territoryMembers.territory__c);
        }
    }
   
    if(terrMembers.size() >0) {
        System.debug('=== This is the territory id: ' + terrMembers);
        Map<Id, Account> newMap= new Map<Id, Account>([Select id, ownerId, Territory__c, fmr__c, territory__r.name,                                                                                                                                                         territory__r.Territory_ID__c                            
                                                                                                            from Account where territory__c =: terrMembers]);

                
            System.debug('-=TerritoryAccountTeamMemberUpdate newMap value :' + newMap);
           
            //calling the helper class AccountServices and the methods in it
            //AccountServices.OnBeforeUpdate(newMap);
            AccountServices.OnBeforeUpdate2(newMap);
            AccountServices.OnAfterUpdate(newMap);
    }

}

This trigger runs perfectly fine while I run it in my dev sandbox, but it is throwing me an error in my fullcopy sandbox. Also I ran this code snippet of Map in Developer Console, where it runs smoothly;
Map<Id, Account> newMap= new Map<Id, Account>([Select id, ownerId, Territory__c, fmr__c, territory__r.name, territory__r.Territory_ID__c                                                                                                              from Account where territory__c =: 'territory ID']);
System.debug('newMap Values are: ' + newMap);

Please let me know if you have any suggestions. 

Thanks!
justin~sfdc
Best Answer chosen by justin_sfdc
GlynAGlynA
Yes, that means it's taking too long.  A couple ways to get more CPU time are to put the code in an @future method (you get 60 seconds of CPU instead of just 10 seconds), or implement it as a Batch Apex job.  Since both of these methods are asynchronous, the trigger and associated DML will complete before the calls to the AccountServices methods are made - but they will be called eventually.  Would this work for you?

-Glyn

All Answers

Norm Sherman SFNorm Sherman SF
Make territory__c indexable by setting it as an external id.
justin_sfdcjustin_sfdc
It is a lookup field; so cant be make an external id.
justin_sfdcjustin_sfdc
Also, we already have 3 external id's in the account object so, cant create another text field and make it an external id.
Is there any other solution that you can think of?
Norm Sherman SFNorm Sherman SF
Sorry, didn't realize it was a lookup field. It probably won't work, but instead of having territory__c =: terrMembers try territory__c IN: terrMembers.

I generally use the IN clause when filtering against lists.

Also don't hesitate to open up a trouble ticket in this case, they generally do a good job in troubleshooting poor query performance and will work with you behind the scenes to make it happen!
GlynAGlynA
Justin,

See if chunking the query up inside a for loop helps (code in next post).

Glyn Anderson
Sr Developer | System Analyst | ClosedWon | closedwon.com
Certified Developer | Certified Advanced Administrator
Twitter: @GlynAtClosedWon
GlynAGlynA
<pre>
Map<Id, Account> newMap = new Map<Id, Account>();
for ( List<Account> accts :
    [   SELECT  Id, ownerId, Territory__c, fmr__c, territory__r.name, territory__r.Territory_ID__c
        FROM    Account
        WHERE   territory__c =: terrMembers
    ]
    )
{
    newMap.putAll( accts );
}
</pre>
GlynAGlynA
Actually, I agree with Norm that line 05 should be:

WHERE territory__c IN: terrMembers

I just forgot to change that bit...

-Glyn
justin_sfdcjustin_sfdc
Hi Norm and GlynA,
I had initially tried it with "IN" in the where clause but had the same result so I tried it with "=" sign to see if it would work with the hardcoded value, but got the same error.

Norm, I did open a case with SFDC already as well, but havent heard anything back, maybe by today I might hear back from them.

GlynA, I tried using the code that you had provided with and it is still showing me the error at the line where we are performing the query; so, the error showing currently is in for loop. The codes gets saved properly but when you test it in the UI performing the action then it is showing the error.

Do you guys think splitting the query up would help as the total number of acct records are about 180K. Any suggestions on how to do it.

Thanks,
Justin~sfdc
GlynAGlynA
Justin,

The code in the following post breaks up the query by ordering by ID and querying IDs you haven't seen yet.

-Glyn

GlynAGlynA
<pre>
Map<Id, Account> newMap = new Map<Id, Account>();

List<Account> someAccounts =
    [   SELECT  Id, ownerId, Territory__c, fmr__c, territory__r.name, territory__r.Territory_ID__c
        FROM    Account
        WHERE   territory__c =: terrMembers
        ORDER BY Id
        LIMIT 10000
    ];

while ( !someAccount.isEmpty() )
{
    for ( Account acct : someAccounts )
    {
        newMap.put( acct.Id, acct );
    }

    Id lastId = someAccounts[ someAccounts.size() - 1 ].Id;
    someAccounts =
    [   SELECT  Id, ownerId, Territory__c, fmr__c, territory__r.name, territory__r.Territory_ID__c
        FROM    Account
        WHERE   territory__c =: terrMembers AND Id > :lastId
        ORDER BY Id
        LIMIT 10000
    ];
}
</pre>
GlynAGlynA
Typo:  the while loop should be:

while ( !someAccounts.isEmpty() )

-Glyn
justin_sfdcjustin_sfdc
Hi Glyn,
 Got this error on trying out:

Apex trigger TerritoryAccountTeamMemberUpdate caused an unexpected exception, contact your administrator: TerritoryAccountTeamMemberUpdate: System.LimitException: Apex CPU time limit exceeded

This basically means its taking too long to process it, right? I had received this error when I tried to change the where clause with like this:
<pre>
map<ID, Account> newMap= new Map<ID, Account> ([SELECT  Id, ownerId, Territory__c, fmr__c, territory__r.name, territory__r.Territory_ID__c
                              FROM Account
                              WHERE territory__r.id IN:terrMembers ] );
</pre>

Thanks,
Justin~sfdc
GlynAGlynA
Yes, that means it's taking too long.  A couple ways to get more CPU time are to put the code in an @future method (you get 60 seconds of CPU instead of just 10 seconds), or implement it as a Batch Apex job.  Since both of these methods are asynchronous, the trigger and associated DML will complete before the calls to the AccountServices methods are made - but they will be called eventually.  Would this work for you?

-Glyn
This was selected as the best answer
justin_sfdcjustin_sfdc
yes, it would. How would i proceed with this. would it work if i put @future at as soon as the class name is defined in the AccountServices class.
But In this class, in all the methods; i have lists/sets/maps as parameters due to which it throws unsupported parameter type List error.

global without sharing class AccountServices
{

    private static final String CLASSNAME = '\n\n AccountServices.METHODNAME()';
   
    // set Account record ownership based on the User associated with the Territory and marked as Primary Record Owner
@future
    public static void OnBeforeInsert(list<Account> accounts) {
        String METHODNAME = CLASSNAME.replace('METHODNAME', 'OnBeforeInsert');
        system.debug(LoggingLevel.INFO, METHODNAME.replace('\n\n ', '\n\n Inside ') + '\n\n');
        Set<String> FMRIdSet = new Set<String>();
        Set<String> TerritoryIdSet =new Set<String>();
        Set<String> TerritoryIdSet1= new Set<String>();
.........
}

Thanks,
Justin~sfdc
GlynAGlynA
You are not allowed to pass any sObjects into an @future method, but you can pass a collection of IDs.  So I'm thinking you can build the set, "terrMembers", in the trigger and pass that to the @future, which would do all the querying and processing.

-Glyn
Kiran  KurellaKiran Kurella
You can pass SObjects to a future method using JSON serialization.  Try the below snippet:

// serialize a Account record
Account acct = [select Id, Name from Account limit 1];
String acctJSON = JSON.serialize(acct);
system.debug('\n ### acctJSON ' + acctJSON);

Invoke the future method by passing acctJSON and de-serialize the string in the future method as follows:

Account newAcct = (Account)JSON.deserialize(acctJSON, Account.class);
system.debug('\n ### newAcct ' + newAcct);


justin_sfdcjustin_sfdc
Hi Glyn,
I did pass the set of id's of terrMembs into the @future class and called the rest of the body of the trigger into this class and was able to pass through the error I was having, however; i am stuck at another error. 
I am actually updating the team members on all the account records whenever a member is added or deleted or updated; There are over 150 account records in total; and whenever the changes the territory id in the account or adds/deletes new members of that territory; i call in a helper class which deletes the previous team members and updates with the current ones. This is where i am having a DML error.

// Remove current Account Team Members
        //List< AccountTeamMember > acctMembers = new List< AccountTeamMember > ([SELECT Id FROM AccountTeamMember WHERE isDeleted = false AND AccountId IN:accounts.keySet() ]);
        //delete acctMembers;     

//The code for that used to be as above and i changed to it as below so that it would be able to perform for bulk
             List< AccountTeamMember > acctMembers = new List< AccountTeamMember > ();
        for(AccountTeamMember acctMember :[SELECT Id, accountId FROM AccountTeamMember WHERE isDeleted = false AND AccountId IN: accounts.keyset()]) {
            //for(AccountTeamMember at: acctMember) {
                if (accounts.containsKey(acctMember.accountId)) {
                    acctMembers.add(acctMember);
                    delete acctMembers;
                    System.debug('=AcctMembs in the if statement now');
                }
           // }
            System.debug('=delete AcctMembs ');
           
        }

On doing this, I am getting Too many DML rows: 10001 error.

The only possible solution i could come up with for this is to write another batch class. Is this the only solution for this? 
Please let me know your thoughts on this.

Thanks!!!
GlynAGlynA
Justin,

I think the problem with your new code is that the delete is inside the for loop, and it delete a list every time - and the list is growing every time.  So the first time through the loop, it deletes one record; the second time, two records; etc.  It will hit the 10000 record limit very quickly this way.

I think what you had originally was better, and it works fine in "bulk".  Try this one line in place of the whole bunch of code:

delete [SELECT Id FROM AccountTeamMember WHERE isDeleted = false AND AccountId IN :accounts.keySet() ];

-Glyn
justin_sfdcjustin_sfdc
Hi Glyn,

I am getting this error message upon the code:
System.LimitException: Too many query rows: 50001

The error is being shown in this same line: delete [SELECT Id FROM AccountTeamMember WHERE isDeleted = false AND AccountId IN :accounts.keySet() ];

When the terrMembers is added or deleted, i want to update into all the account records just upon refresh. In order for that to happen the way I'm currently pursuing it is by deleting the old members and updating it with the new ones. If i do not perform the delete operation then it will keep on adding the team members again and again and so on.Since the total number of account records are about 180K, it runs the query against all those records, hence getting into this error.

Thanks,
Justin~sfdc

GlynAGlynA
Is "accounts.keySet()" that large?  I was under the impression that you weren't processing that many records at a time.  It sounds like this process is not appropriate to be done in a trigger.  You probably need to write a Batch Apex class.  Could this be done in a nightly batch job instead of a trigger?

Help me understand how this is being used and what it's trying to accomplish, and I'll try to help come up with the proper architecture.

-Glyn
justin_sfdcjustin_sfdc
Hi Glyn,
we have an account table that has a lookup with territory. Every account has a territory id; and territory has master detail relation with territory team members. So, if the territory 'x' has members 'x,a,b,c,d' then in the related list of the account where the territory is x, the account team members related lists will show, x,a,b,c,d. 
If one goes and adds a member lets say 'e' for that territory x then the account team members should now show 6 team members x,a,b,c,d,e
There are total of 180K account records and all of them have one territory out of 9. 
so, the trigger i wrote was if the user adds/deletes/updates any territory member, then it should go ahead and make the changes in the account record automatically. Currently, if i make the change in the territory member, it's not updating the account team members, but if i were to change the territory in that account from x to y and then back to x then it will show the correct members, but we dont want to do that in all the thousands of records.
Please let me know if you have any question on the design.

Thanks a lot, Glyn!
GlynAGlynA
Justin,

My first thought is:  If it is not essential that the account team updates immediatlely, this could be done in a nightly batch job.  The batch "start" method would look for any territory team members that have been modified since the last update, then return a query locator that returns all accounts that lookup to the territories of the modified team members.  This would average 20K records per territory, so there's no easy way to do this outside of batch apex.

You could use a custom setting to record the last update timestamp and code to handle a null timestamp.  This would allow the first batch run to update all of the accounts.  Subsequent runs would change only those accounts that have been affected by a team member change.

If you need to update the accounts "immemdiately", I think you'd still need to kick off a batch job, but you'd launch a batch job for each team member change.  This could cause a lot of batch jobs, if multiple team members change per day.  And the results wouldn't actually be available for several minutes after the original transaction.

Which solution is best probably depends on how often team members change, and whether they change one at a time or several at a time.

Let me know what you think.

-Glyn
justin_sfdcjustin_sfdc
Hi Glyn,

I think the batch job would be better to work on as the changes does not need to happen right away. I like the idea of querrying based on the last update. Thanks a lot!