I have a method that gets a count of contacts associated with a custom object Region__c and then gets a count of the tasks assigned to those contacts. If I run an update in Data Loader on Region__c I will hit the 50,000 rows limit unless I turn batch size down to 5. (Not a huge problem right now because there are only 16 region__c records) As a new developer I am not certain if there inefficiencies in my code that are causing the problem if I should refactor the code to a batch method. I was hoping to get some advice from all of you.

public static void uniqueRegionEmails(Set<id> regionIdList){
    //count the unique emails  that have been  recieved by Influencers 
    //Assigned to a region
    // add all contacts associated with region to map. 
    // create list from Ids in Trigger 
    List<Region__c> regionList = [SELECT Id, Unique_Emails_Sent__c FROM Region__C
                                 WHERE Id IN : regionIdList];
    system.debug('Number of regions in trigger' + regionList.size());        
    Map<id, Contact> contactMap = new Map<Id, Contact>([SELECT Id,
                                                        FROM Contact
                                                        WHERE region_lookup__c IN : regionList]);
    system.debug('Number of regions in trigger = ' + regionList.size());
    system.debug('Contacts found = ' + contactMap.size());
    //put tasks where whoId is in the contact map into a new map
    List<Task> taskList = [SELECT Id, WhoId,Subject
                           FROM Task 
                           WHERE WhoId IN :contactMap.keySet()
                           AND Subject LIKE '%Pardot List %'];
    system.debug('Tasks Found  and added to map = ' + taskList.size());

    //use set to dedupe the list
    Map<Id, Set<String>> subjectLineMap = new Map<Id, Set<String>>();

    for(task t : taskList){
        Id regionId = contactMap.get(t.WhoId).region_lookup__c;            
            subjectLineMap.put(regionId, new Set<String>());
            system.debug('Subect Line found ' + t.Subject);              

        Set<String> subjects = subjectLineMap.get(regionId);
        subjectLineMap.put(regionId, Subjects);

    system.debug('Map size   =' + subjectLineMap.size());
    system.debug('map values =' + subjectLineMap.values());
    system.debug('map keys   =' + subjectLineMap.Keyset());        
        for(region__c region : regionList){
            if(subjectLineMap.containsKey(region.Id) && 
               subjectLineMap.get(region.Id).size() != Null){
            region.Unique_Emails_Sent__c = subjectLIneMap.get(region.Id).size();

  • 2
    size() will never return null, so that's your first problem. I feel you might be able to use aggregate result queries, too, to greatly reduce the number of rows returned. – sfdcfox Nov 1 '18 at 20:40
  • 1
    Yes to aggregates, with the most recent update I believe that Count(Id) aggregates only count as 1 row. – gNerb Nov 1 '18 at 21:48

If I understand your data model properly, what you would like to do is get the count of subject lines of Tasks whose Subject matches the pattern '%Pardot List %', grouped by the Region assigned to the Contact to which the Tasks are linked.

The fact that you cannot query most fields across the WhoId relationship makes this much more complex than it might be with objects that don't use polymorphic relationships. Otherwise, you could do a nice, efficient aggregate query like

 SELECT count(Subject), Contact__r.Region_Lookup__c
 FROM Task
 WHERE Subject LIKE '%Pardot List %'
 GROUP BY Contact__r.Region_Lookup__c

But I don't see a way to use aggregate queries here, because you don't have a relationship path that would allow you to do that type of grouping.

What I'll suggest instead is to denormalize this data to compensate for the limitations of the WhatId relationship. Have a trigger copy the Region_Lookup__c into a new field on the Task whenever its WhoId changes or is populated. This field doesn't have to be a lookup, although it can be - you could choose to use a text field with the region name instead.

Then, you can run a single query - no maps or fancy calculations:

 SELECT count(Subject), Region_Field__c
 FROM Task
 WHERE Subject LIKE '%Pardot List %'
 GROUP BY Region_Field__c

Loop over the resulting AggregateResult records and you can easily update all of your Region records' totals. The query is limits-cheap because it'll only cost you N SOQL rows for N regions represented, although it may be rather time-expensive to execute.

  • David that sounds like a great idea. Thank you. I will try it out! – Brooks Johnson Nov 2 '18 at 10:47
  • The other requirement is to get a count of unique subject lines. If a region has 1000 tasks with the subject Pardot List Email 1 then they get credit for one unique email. Will this prevent me from using aggregate queries? I still need to loop through all the subject lines and assign them to a set. – Brooks Johnson Nov 2 '18 at 10:54
  • 1
    No, this is an aggregate query. The result of the final query shown here would be the count of subject lines per region. – David Reed Nov 2 '18 at 10:55
  • 1
    Brooks, I think we're just agreeing in different terms! I'm pretty sure the aggregate query shown above will serve your needs. Can you say more about your concern? – David Reed Nov 2 '18 at 12:16
  • 1
    You bet! Let me know if it works out. – David Reed Nov 2 '18 at 12:25

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.