Apex Code Development You need to sign in to do that
Don't have an account?
justin_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
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
-Glyn
All Answers
Is there any other solution that you can think of?
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!
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
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>
WHERE territory__c IN: terrMembers
I just forgot to change that bit...
-Glyn
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
The code in the following post breaks up the query by ordering by ID and querying IDs you haven't seen yet.
-Glyn
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>
while ( !someAccounts.isEmpty() )
-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
-Glyn
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
-Glyn
// 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);
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!!!
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
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
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
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!
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
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!