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];
}
}
From my experience working with the CampaignMember object, I see the following scenarios:
1. CampaignMember
with Lead
CampaignMember.LeadId
is populated
CampaignMember.ContactId
is null
2. CampaignMember
with Contact
CampaignMember.LeadId
is null
CampaignMember.ContactId
is populated
3. CampaignMember
with Contact
who is already a converted Lead
CampaignMember.LeadId
is null
CampaignMember.ContactId
is populated
4. CampaignMember
with Lead
, then Lead
is converted to Contact
CampaignMember.LeadId
is populated
CampaignMember.ContactId
is also populated
In writing custom logic to determine if I should be using the Contact
or Lead
lookup on CampaignMember
, I first check if ContactId
is populated. If it's not, then I fallback to the Lead
.
Best Answer
As a start, the 'Leads__r' relationship is based on a custom field, as noted by the __r at the end, while convertedContactId is a standard field. Off the bat you're querying via the wrong relationship.
Looking at the schema it looks like there's no way to use a subquery (what your join is technically called in SOQL) as there's no relationship name. This is a quirk that can only happen with customer foreign-key (aka lookup) relationships. This means you'll need to change your logic to either
SOQL is odd in that it doesn't join in the traditional way, and instead uses subqueries in one direction and cross-object fields (e.g. querying lead for convertedContact.name) in the other. (there's also semi-joins and anti-joins, but that's getting longwinded)