1

In our Salesforce deployment, we have two account types (partner and merchant). Merchants have a custom field that contains their parent "partner" account.

I am trying to write a trigger (on account add/removed/modify) that will count the merchants and updated a field in the partner with the total number of merchants. I keep running into limits due to having a large data set of merchants and partners. Even after indexing the custom fields.

Is there a better way to query this information?

Trigger

trigger MCS_CountPaidMerchantTotals_Trigger on Account (after insert, after update, after delete) 
{
    if(MCS_CountPaidMerchantTotals_Class.runME == true)
    {
        MCS_CountPaidMerchantTotals_Class.countMerchants(Trigger.New);
    }
}

Class

public class MCS_CountPaidMerchantTotals_Class
{

    public static boolean runME = true;  // Prevents trigger recursion

    public static void countMerchants(Account[] accounts) 
    {
        for (Account A : accounts) 
        {  
            //---- Count Paid Merchants for Partner ----
            Integer pcount = [SELECT COUNT() FROM Account Where Account_Status__c='Paid' AND Partner_Parent_Name__c=:A.Partner_Parent_Name__c];

            //---- Retrieve primary Partner ----
            Account cc = [SELECT Id FROM Account WHERE Name=:A.Partner_Parent_Name__c AND (Account_Status__c='Paid I2M Partner' OR Account_Status__c='Paid D2M Partner')];

            //---- Update Partner Count Field ----
            cc.Number_Active_Merchants__c = pcount;

            runME = false;   // Prevent recursion
            update cc;
        }
    }
}
2
  • 1
    First. you must not do SOQL queries in for loops. What error you're getting ? Apr 16 '14 at 14:08
  • 1
    " I keep running into limits" i think we can safely assume those are the soql & dml limits. Apr 16 '14 at 14:22
1

The issue is not the composition of your queries, but the way your trigger(process) is interacting with the database. You've correctly understood that a trigger can or may process a batch of records and should thus be coded according to this expectation.

Your current approach will interact with the database separately. For each and every account in your trigger execution you're performing 2 queries and a DML update action. 3 database interactions per record is a whole lot of database interactions, something that costs relatively much performance and efficiency to do.

This is why salesforce enforces the governor limits that you have been hitting. The solution is to think and implement your process differently.

  1. iterate all your accounts collecting their Partner_Parent_Name__c values.
  2. Query for your count() information in a single query for all parent values, you will most likely need a GROUP BY aggregated query. Probably you will want to process this result and store it in a Map structure, for easier matching.
  3. Query for all the related primary partners
  4. Update all primary partners with the corresponding count data.
  5. Update all primary partners to the database in a single DML statement.

This way of doing things takes some getting used to, as other platforms usually do not enforce this. Yet using these algorithms usually also result in more efficient code in other platforms.

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.