I am running into performance issues while dealing large data volume in batch apex.I believe the place where its taking more time is the execution part where we are doing a lot of processing [updates/inserts etc.,]. While we are working on fixing that part, We also have a SOQL to retrieve the data to be worked upon and I want to understand if there is any improvement that needs to be done in that as well.
I would like to hear your opinion on the differences between the approaches for , is one more efficient that the other.
There are 2 custom objects Object A and B with A being the master and B being the child. A has close to 3 million records and B has 16 million+ records. For each record in object A on an average there are 5 child records in object B. type__c is picklist and is not indexed.
We have a batch job with batchsize 2000 that queries object A and for every record in object A it fetches the list of child records in object B and does some business logic on that. With that background the question I have is,
Approach 1:
[SELECT Id , Name, (SELECT Id,Name
FROM Permissions__r
WHERE type__C = 'TYPEA'
)
FROM Contact__c
WHERE Contact_ID__c IN :ContactIDList
];
Approach 2:
[SELECT id ,Name,Contact__r.Name
FROM Permissions__c
WHERE contact__ID__c IN :ContactIDList
AND type__C = 'TYPEA'
];
- In Approach 1, what is the sequence of execution, My assumption is the outer soql is executed first and the child records only for those contacts are fetched. But I have folks that say that the inner query gets executed first fetching all permissions of TYPEA.
- Is there a difference between Approach A Vs Approach B in terms of efficiency?
thanks for your assistance.