Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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.

share|improve this question

1 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?

share|improve this answer
    
Thanks for your detailed response @eyescream. I am trying to compare both the approaches. Based on my initial analysis I don't see much of a difference in both the approaches but the environment I am trying has limited data set. On your response, point # 3, Did you get all records from both contacts and accounts when you the subquery approach. This is different from what I am seeing when I ran a similar query. I got only the contacts which where associated with accounts and not all contacts. –  user7743 May 14 at 13:48
    
Yep, I got them all (10 contacts don't have an account, check what does the last query return for you?). Do you have "View all data" in that environment? Contacts without Account are considered private, visible to the sysadmins and their owners only and no amount of sharing can help on it. Seems I've picked bad sample object to illustrate the differences... –  eyescream May 14 at 15:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.