SOQL execution order

Question

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'
];

1. 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.
2. Is there a difference between Approach A Vs Approach B in terms of efficiency?

thanks for your assistance.

Answer 1

https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

In no particular order:

1. Don't believe, apply a debug log and experiment ;), fire off your batch and check the timestamps between SOQL_EXECUTE_BEGIN and SOQL_EXECUTE_END. Go to job monitoring, maybe the job spends almost 2 minutes in the preparing state (= running the initial querylocator)? As a rule of thumb I'd I believe should be similar and you should consider other factors.

2. Before thinking about performance think about your business logic. What are you doing with Contacts that don't have any permissions because of the WHERE applied? Just skipping them? Does your action match the approach 2 & two loops or maybe a flat list looks cleaner? I'm trying to say: make it easy to understand and maintain before doing performance improvements.

   • Are you updating Contacts or Permissions in that batch or is it more of a readonly run through them? If you do update them - are you aware that you should ideally have FOR UPDATE slapped at the end of the query - but I'm not sure if in version 2 this will cascade down to subquery rows too and in version 1 - up to the contact...

   • if you're in version 1 and updating the contact - does your logic handle the fact that a Contact with 5 permissions can be "sliced" so 2 Permissions end up in 1st execute() and remaining ones - in the next execute() chunk?

3. Are you aware that these waste different amount of query rows? In my Developer Edition I have 33 Contacts and 27 Accounts:

   List<sObject> objs = [SELECT Id, Name, Account.Name FROM Contact]; // 33 rows
   List<sObject> objs = [SELECT Name, (SELECT Id, Name FROM Contacts) FROM Account]; // 50 rows
   // SELECT COUNT() FROM Account WHERE Id NOT IN (SELECT AccountId FROM Contact)
   // returns 13
   // SELECT COUNT() FROM Contact WHERE AccountId = null
   // returns 10
   

   So version 2 seems to waste all 33 + 27 rows even though there clearly are 10 contacts without account that shouldn't be included. Looks like your "some folks" might be right as counter-intuitive as that sounds.

   With 50 K rows fetched limit the version 2 would use up 2000 * (1 contact + 5 permissions) = 16 K. That's not yet close to limit but can easily blow up.

4. Where does your list of Contact Ids come from? maybe you'd be better off pushing that query to start() or making more complex query that will save on the database roundtrips FROM Contact WHERE Account.Owner.isActive = true for example?

5. Do you know that version 2 can sometimes "lie" and result only first 100 rows from the related list for example? It's something similar to queryMore() mechanism from SOAP API.

6. What's the data skew? Does "TYPEA" account for say 40% of all rows? Is it always for that type? Could you consider having a workflow that copies picklist value to helper text field marked as external id (not unique, only ext id) and then filter on that helper field?