6

ISSUE:

I have a SOQL of that returns a reasonable amount of rows of Campaign based on Contact Ids (i.e. 200 batch of contacts maybe 1000 connected Campaigns) But it also returns an relationship query column of CampaignMembers which I believe is being referred to as an Aggregate query in the error I get during bulk INSERTS/UPDATES:

System.DmlException: Insert failed. 
First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY,
Quote: execution of AfterInsert

caused by: System.QueryException: 
Aggregate query has too many rows for direct assignment, use FOR loop

Its possible that if 1000 campaigns rows come back there could be 50+ CampaignMembers listed in this aggregate column. From what I can tell, whatever the numbers are its too many and causing this error. When in Sandbox or any instance where there is not enough data to have these results everything works as perfectly as expected.

QUESTION:

Do I need to use Batch Apex for this one SOQL? If so any idea how I would do that? (I've never done it before but just read about it on this forum here)

REFERENCE:

Here is my soql statement for reference of what I am returning

SELECT Id, StartDate, EndDate, 
    (SELECT Id FROM CampaignMembers 
     WHERE ContactId IN :contact_list AND
           CampaignId IN :campaignIdList) 
   FROM Campaign 
   WHERE Id IN :campaignIdList AND
         StartDate != null AND 
         EndDate != null AND 
         RecordTypeId IN (SELECT Id FROM RecordType 
                           WHERE Name = \'Personal Touch\') 
   ORDER BY StartDate ASC LIMIT 50000

As you can see I've tried to add WHERE statements to limit the returned values as much as possible, so even the CampaignMembers in the aggregate column are reduced to only the Campaigns that I'm expecting.

  • Is the purpose of your query to get all contacts for a given time period? / Why are you updating so many records? Can you limit the number of campaigns by start and end date? – Christian Deckert Feb 12 '15 at 8:41
  • you are missing a comma after EndDate in line 1 - is this a typo? – cropredy Feb 12 '15 at 17:17
  • @ChristianDeckert the purpose in this instance is to map all the CampaignMembers effected by ContactId's touched when Quotes are updated by their Campaign. So the end result looks something like Map<CampaignId>,List<CampaignMember>> – Xtremefaith Feb 12 '15 at 18:55
  • Your error is not due to an sql error it is due to the fact that the returned records cannot be directly assigned to a map. You need to use a for(Object a : query) map.put(key,a); to populate the map instead of the map xxx = New Mapxxx([Query]); – Eric Feb 12 '15 at 22:15
  • @Eric is that only true because of how many rows are being returned? Because otherwise the map populates just fine. I had done this because I learned about it here: salesforce.stackexchange.com/questions/5288/… – Xtremefaith Feb 12 '15 at 22:18
3

I assume that you already considered to use a for loop without assigning the query result to List. (The "For loop list batch size" will apply.)

However, let's improve the overall design:

The problem in detail

In apex every FROM statement counts as a separate SOQL query. Your query contains 3 FROM statements.

In a SOQL query with parent-child relationship sub-queries, each parent-child relationship counts as an additional query. - Understanding Execution Governors and Limits

Almost every salesforce limit applies only for one execution context. This means you can create a bunch of execution contexts and you will workaround limits.

The batchable interface works as follows:

The start method is called at the beginning of a batch Apex job. Use the start method to collect the records or objects to be passed to the interface method execute. This method returns either a Database.QueryLocator object or an Iterable that contains the records or objects being passed into the job. - Using Batch Apex

And:

Each execution of a batch Apex job is considered a discrete transaction. For example, a batch Apex job that contains 1,000 records and is executed without the optional scope parameter from Database.executeBatch is considered five transactions of 200 records each. - Using Batch Apex

Remember: Every call of execute() creates a new execution context. Therefore you are able to query more records. However, the following solution reduces the number of query per execution context to 1. And lowers the number of queried records.

Query Enhancements

1. Remove unnecessary queries:

Replace the following line

RecordTypeId = (SELECT Id FROM RecordType WHERE Name = \'Personal Touch\')

with

RecordType.Name =  \'Personal Touch\'

2. Split your query in 2 different queries

This will allow you to use Batch functionality.

In a first step you have to query the campaigns:

SELECT Id, StartDate, EndDate FROM Campaign WHERE [CRITERIA] Limit ??

In a second query all CampaignMembers

SELECT Id FROM CampaignMembers WHERE CampaignId IN :[Campaign ID List] LIMIT 10000

3. Make sure that the functionality works fine (in your sandbox)

Batchify your program

Create a new class which implements the interface Database.Batchable make sure that you are using Database.QueryLocator. Move your campaign query to the batchable class and call your "functionality class" in the execute statement.

Your batchable class should look similar to this:

global class SearchAndReplace implements Database.Batchable<sObject>
{

   global final String query = 'SELECT ID FROM Campaign WHERE ??';


   global Database.QueryLocator start(Database.BatchableContext BC)
   {
      return Database.getQueryLocator(query);
   }

   global void execute(Database.BatchableContext BC, List<sObject> scope)
   {
        FunctionalityClass.execute((List<Campaign>) scope);
    }

   global void finish(Database.BatchableContext BC)
   {
   }
}

And your functionality class:

class FunctionalityClass
{
    public static void execute(List<Campaign> campaigns)
    {
        List<CampaignMember> cm = [SELECT ID FROM CampaignMember WHERE ID IN :campaigns];
    }
}
  • 3
    Nice answer! Just want to say that it is better to use a DeveloperName while using RecordTypes, it is unique. – Sergej Utko Feb 12 '15 at 9:25
  • in FunctionalityClass.execute() did you mean WHERE CampaignId IN :campaigns? – Xtremefaith Feb 12 '15 at 21:45
  • If I'm going through the trouble to have an additional SOQL statement then why do I need to Batchify? I figured I would learn how to batchify as a means of returning an optimal SOQL statement RATHER than have a separate queries so that I can stay on the lower end of the governing limits – Xtremefaith Feb 12 '15 at 21:48
  • @Xtremefaith you have to reduce the number of rows per execution context. There are 2 solutions: 1st limit the number of results to < 50k, < 6 MB or limit the number of rows per execution context... – Christian Deckert Feb 12 '15 at 22:25
  • 2
    You mention every FROM statement is a separate query, but when I execute anonymous I get this reported: Number of SOQL queries: 1 out of 100 (not 3 as I would have expected per your answer). Also, when I replaced my RecordType statement with yours my Maximum CPU time: went from 81 to 109 out of 10000. Not sure what that matters but it seems the lower the better – Xtremefaith Feb 12 '15 at 23:24

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.