+ Start a Discussion
Greg RohmanGreg Rohman 

Assistance with governor limits please

Hello.

 

I've attemped the following logic numerous times (as a trigger, trigger calling apex, batchable, @future) and every attempt I've run into governor limits. I'm not sure how to achieve what I'm looking to do.

 

Basically, I have a custom object called Gift, that contains a lookup to Account. The Account object has a string field called TAGS_Applic_Codes that contains a 5-character unique identifier to a legacy system. In our legacy system, though, it's possible for a single account to have multiple codes (different addresses, for example, will each have their own code, and thus be a separate Account record in Salesforce). As such, the Account object also contains a string field called Related_TAGS_App_Codes that contains a comma delimited listing of all of the TAGS applic codes that should be treated together.

 

The Gift object contains a currency field called Total_Cost, and the Account object contains a field called X2010_Gift_Expenditure_Total. What I'm looking to accomplish is that whenever a gift record is created, I want to calculate the total of all of the gifts for that particular Account AND any related accounts through that additional field, and store them in the X2010_Gift_Expenditure_Total field on the Account object (and related records).

 

Below is a semi-functional Trigger version of my code, so you can see my thought process. In this particular version, I'm getting a "too many query rows" error, but my governor errors have ranged from that, to "too many SOQL queries: 21", to limits on batchables and @future calls.

 

 

 

trigger trigUpdateCompanyGiftTotal on Gift__c (after insert, after update, after delete) {


List<Id> changedAccountIds = new List<Id>();
List<Account> changedAccounts = new List<Account>();
List<Account> allAccounts = new List<Account>();
List<Gift__c> allGifts = new List<Gift__c>();
Map<Id,Account> linkedAccounts = new Map<Id,Account>();

// Store all accounts and gifts to avoid governor limits
for (Account acc: [SELECT id, name FROM account]) {
allAccounts.add(acc);
}
allGifts = [SELECT Id FROM Gift__c];


//Loop on each changed gift entry. For deletes, need trigger.old
if (Trigger.isDelete) {
for(Gift__c g : Trigger.old){
if (g.Company_Firm__c <> Null) {
changedAccountIds.add(g.Company_Firm__c);
}
}
} else {
for(Gift__c g : Trigger.new){
changedAccountIds.add(g.Company_Firm__c);
}
}


// In triggers, lookup fields do not contain references to the entire object... they only contain the Id.
// So, need to get the actual changed Account objects from the stored IDs.
for (Id changedId: changedAccountIds) {
changedAccounts = [SELECT Id,Related_TAGS_App_Codes__c FROM Account WHERE Id=:changedId];
}

for (Account a: changedAccounts) {
//Determine if any related accounts by a nested loop over the full account list
if (a.Related_TAGS_App_Codes__c <> null && a.Related_TAGS_App_Codes__c.length() > 0) {
for (Account accLinkCheck: allAccounts) {
if (a.Related_TAGS_App_Codes__c.contains(accLinkCheck.Tags_Applic__c)) {
linkedAccounts.put(a.Id,accLinkCheck);
}
}
} else {
linkedAccounts.put(a.Id,a);
}
}


// Loop on all gifts, adding the totals and storing it back in the account and any related accounts
Decimal totalGift2009 = 0;
Decimal totalGift2010 = 0;

for (Gift__c g: allGifts) {
if (linkedAccounts.containsKey(g.Company_Firm__c)) {
if (g.Date__c.year() == 2009) {
totalGift2009 = totalGift2009 + g.Total_Cost__c;
}
if (g.Date__c.year() == 2010) {
totalGift2010 = totalGift2010 + g.Total_Cost__c;
}
}
}


// Update the total for each account
for (Account acc: linkedAccounts.values()) {
acc.X2009_Gift_Expenditure_Total__c = totalGift2009;
acc.X2010_Gift_Expenditure_Total__c = totalGift2010;
update acc;
}

}

 

My last resort would be to eliminate the trigger portion of this and just have a batch process that runs the update at set intervals, but I'd really prefer to avoid that and have it update when a Gift record is added/changed. I just don't see a way to do it without hitting some sort of governor limit.

 

Any advice is greatly appreciated. Thank you.

 

 

-Greg

Best Answer chosen by Admin (Salesforce Developers) 
Jeremy.NottinghJeremy.Nottingh

I see what your situation is; you've got a bunch of Accounts, probably more than 10000. Each one has data stored in a field that isn't really accessible to a normal SOQL query. So your idea is to go through all of them and use the data you extract from that field to add up all the Gifts for all the accounts that are associated (through the funny field). Unfortunately, Salesforce won't let you do that, because it's not really made to query that many records at once, and you can't use the data field for query filtering

 

One idea: Change this from a Trigger to a Batch Apex process. Maybe it runs every night. That will allow you to query all of your Accounts and Gifts without running into Governor issues. There really is no other way to avoid the SOQL query limits that I can see.

 

Another idea: Instead of storing this related account data inside a text field, create object relationships that symbolize this same data. For example, make a connector object ("TAGS_Applic_Code__c" maybe) between Gift and Account. Each Account could have several of these, maybe even one marked Primary. Each Applic Code object could have several Gifts on it. Then you can actually query these objects more specifically, without carrying around this embedded CSV data. And the structure of the Account and related objects is obvious from looking at the structure. It would take some work to get there, but it would make a lot of things easier in the future, I'm guessing.

 

Let me know what you think.

 

Jeremy

All Answers

Jeremy.NottinghJeremy.Nottingh

The trick with governor limit problems is usually queries inside loops. In your case, you have this one:

 

// In triggers, lookup fields do not contain references to the entire object... they only contain the Id.
    // So, need to get the actual changed Account objects from the stored IDs.
    for (Id changedId: changedAccountIds) {
        changedAccounts = [SELECT Id,Related_TAGS_App_Codes__c FROM Account WHERE Id=:changedId];
    }

If you have more than about 15 Accounts involved, this will definitely blow your limits. If you make a set of changed Account IDs, you can query on all of them at once:

 

 

    set<id> changedAccountIdSet = new set<id>();
    changedAccountIdSet.addall(changedAccountIds);
    list<Account> allChangedAccounts = [select Id,Related_TAGS_App_Codes__c FROM Account WHERE Id in :changedAccountIdSet];

Incidentally, you're also doing a DML update inside a loop later on:

 

 for (Account acc: linkedAccounts.values()) {
        acc.X2009_Gift_Expenditure_Total__c = totalGift2009;
        acc.X2010_Gift_Expenditure_Total__c = totalGift2010;
        update acc;
    }

 Take the update statement at the end:

 

update linkedAccounts.values()

 

 

When in doubt, do it in bulk!

 

Jeremy

 

aalbertaalbert

Jeremy is right on. Also, check out this article on Apex Best Practices. It covers similar topics and a few additional ones.

Greg RohmanGreg Rohman

Hi Jeremy, and thanks for the response. I am familiar with bulkifying apex and triggers, and most of the other code I've written is done that way. My problem with this particular one is that I can't quite figure out the logic to accomplish what I'm looking for.

 

The trigger is on the Gift object, but since I need to relate it to any number of Account records as well as other Gift records related to those accounts, the only way I can see to accomplish it is to query ALL of the Account and Gift records to find those that are related. I was hoping that I could do a query to get all of the Accounts and Gifts and store them in Lists outside of the loop, but there seems to be a 1000 record limit (either on query results or list size), so that wasn't working. The version for Accounts below, incidentally, was taken from the Apex documentation as a way to get around governor limits, but it doesn't seem to work for me:

 

 

    for (Account acc: [SELECT id, name FROM account]) {
allAccounts.add(acc);
}
allGifts = [SELECT Id FROM Gift__c];

 

 

If there was a way to query all of the Account and Gift records and store them, bulkifying the rest of the trigger is fairly straightforward from there.

 

Thanks, and look forward to your additional insight.

 

-Greg

Jeremy.NottinghJeremy.Nottingh

I see what your situation is; you've got a bunch of Accounts, probably more than 10000. Each one has data stored in a field that isn't really accessible to a normal SOQL query. So your idea is to go through all of them and use the data you extract from that field to add up all the Gifts for all the accounts that are associated (through the funny field). Unfortunately, Salesforce won't let you do that, because it's not really made to query that many records at once, and you can't use the data field for query filtering

 

One idea: Change this from a Trigger to a Batch Apex process. Maybe it runs every night. That will allow you to query all of your Accounts and Gifts without running into Governor issues. There really is no other way to avoid the SOQL query limits that I can see.

 

Another idea: Instead of storing this related account data inside a text field, create object relationships that symbolize this same data. For example, make a connector object ("TAGS_Applic_Code__c" maybe) between Gift and Account. Each Account could have several of these, maybe even one marked Primary. Each Applic Code object could have several Gifts on it. Then you can actually query these objects more specifically, without carrying around this embedded CSV data. And the structure of the Account and related objects is obvious from looking at the structure. It would take some work to get there, but it would make a lot of things easier in the future, I'm guessing.

 

Let me know what you think.

 

Jeremy

This was selected as the best answer
Greg RohmanGreg Rohman

Hi Jeremy.

 

I have a version of the trigger completely bulkified and ready to be submitted as a scheduled batch job... I was just hoping that there was another way that would provide more "real-time" updates.

 

Your suggestion of a linking object rather than a text field is also something that I have been considering, but like you mentioned, it would involve rewriting some other code that's currently utilizing that field. For the long term, though, it's probably the best solution.

 

Thanks for the suggestions.

 

-Greg

Jeremy.NottinghJeremy.Nottingh

One thing I thought of after I wrote that is that you could make it a batch job, but make it kick off from a Trigger. You would have to make sure you didn't have too many of them running at once, but at least it would be more responsive than having to wait until the next day for results. 

 

I have a batch job like this that runs on request, and then emails the user when it's complete; usually within 10 minutes or so. You can only have 5 Batch jobs running at once, so your Trigger would have to manage the queue. Depending on how often the Trigger fires, that may never be a problem.

 

Good luck,

 

Jeremy

Greg RohmanGreg Rohman

Hi Jeremy.

 

Interestingly enough, I tried exactly that approach too, but ran into the "too many batches" issue. I didn't explore it too much further, as that method didn't seem to be the most efficient. Similarly, I created a version using the "@future" directive, but ran into the same problem.

 

-Greg

Greg RohmanGreg Rohman

Hi Jeremy.

 

I managed to find a workable solution for this. It's decently bulk-ready, in that all queries have been moved out of loops. There are definitely instances where governor limits might be hit (record limits in trigger queries, for example), but my org should never encounter them. It might not be the most efficient code, but it accomplishes what I was looking to do.

 

 

 

trigger trigUpdateCompanyGiftTotal on Gift__c (after insert, after update, after delete) {


List<Id> changedAccountIds = new List<Id>();
List<Account> changedAccounts = new List<Account>();
List<Account> allAccounts = new List<Account>();
List<Gift__c> allGifts = new List<Gift__c>();
Map<Id,Account> linkedAccounts = new Map<Id,Account>();

//Loop on each changed gift entry. For deletes, need trigger.old
if (Trigger.isDelete) {
for(Gift__c g : Trigger.old){
if (g.Company_Firm__c <> Null) {
changedAccountIds.add(g.Company_Firm__c);
}
}
} else {
for(Gift__c g : Trigger.new){
changedAccountIds.add(g.Company_Firm__c);
}
}

// In triggers, lookup fields do not contain references to the entire object... they only contain the Id.
// So, need to get the actual changed Account objects from the stored IDs.
Set<Id> changedAccountIdSet = new Set<Id>();
changedAccountIdSet.addall(changedAccountIds);
changedAccounts = [select Id,TAGS_Applic__c,Related_TAGS_App_Codes__c FROM Account WHERE Id IN :changedAccountIdSet];

// This will get the related tags codes for any of the changed accounts, and add them to a
// new list called changedAccountsRelated
Set<string> relatedTagsCodes = new Set<String>();
for (Account a: changedAccounts) {
if (a.Related_TAGS_App_Codes__c <> NULL && a.Related_TAGS_App_Codes__c.length()>0) {
relatedTagsCodes.addall(a.Related_TAGS_App_Codes__c.split(','));
}
}
List<Account> changedAccountsRelated = new List<Account>();
changedAccountsRelated = [SELECT Id,TAGS_Applic__c,Related_TAGS_App_Codes__c FROM Account WHERE TAGS_Applic__c IN :relatedTagsCodes];


// append the changedAccountsRelated list and the changedAccounts list to a new Set
// called allChangedAccounts to eliminate duplicates
Set<Account> allChangedAccounts = new Set<Account>();
allChangedAccounts.addall(changedAccounts);
allChangedAccounts.addall(changedAccountsRelated);

// Convert the set to a list to prevent an "internal server error" when running the query below
List<Account> allChangedAccountsList = new List<Account>();
allChangedAccountsList.addall(allChangedAccounts);

// Get all gifts on any of those accounts
List<Gift__c> accountGifts = new List<Gift__c>();
accountGifts = [SELECT Id, Date__c, Total_Cost__c FROM Gift__c WHERE Company_Firm__c IN :allChangedAccountsList];


// Loop on account gifts, adding the totals and storing it back in the account and any related accounts
Decimal totalGift2009 = 0;
Decimal totalGift2010 = 0;
for (Gift__c g: accountGifts) {
if (g.Date__c.year() == 2009) {
totalGift2009 = totalGift2009 + g.Total_Cost__c;
}
if (g.Date__c.year() == 2010) {
totalGift2010 = totalGift2010 + g.Total_Cost__c;
}
}

// Update the total for each account
for (Account acc: allChangedAccountsList) {
acc.X2009_Gift_Expenditure_Total__c = totalGift2009;
acc.X2010_Gift_Expenditure_Total__c = totalGift2010;
}
update allChangedAccountsList;

}

 

This is working well, and I have 100% unit test coverage... but if you happen to see any glaring issues, please let me know.

 

Thanks again.

 

-Greg