2

Considering the following scenario:

Objects: Child__c, Parent1__c, Parent2__c

Relationships:

  • Child__c - Parent1__c (Lookup)
  • Child__c - Parent2__c (Master-detail)

I want to query for Parent1 and Parent2 (Many Parent2 will be related to Parent1) and I want to keep the relationship between them (introduced through Child__c). Child__c is just a junction object in this case.

I have 2 options:

Pseudo SOQL:

Select Child__c.Parent1__r, Child__c.Parent2__r from Child__c

or 

Select Id, Name, ..., (Select Parent2__r.Id, Parent2__r.Name FROM Child__r) FROM Parent1__c

Because the relationship is not that simple, SOQL number 2 fits my purpose better.

However, I'm concerned of the performance of the nested SELECT statement.

So my main question is, are these cases optimized? This would be a regular JOIN in any SQL language.

Would I suffer any perofmance degradation in this case?

Any reason to prefer option 1 over option 2 or vice versa?

| improve this question | |
  • With Option 1, you are going to have to use an apex loop to reconstruct the relationships, but option 2 has already grouped them by Parent1. Option 2 might be a little less efficient in SOQL, but the savings in logic to process them is also a factor. – Jeremy Nottingham Jan 23 '17 at 13:58
2

You can see some incredible depth on query cost in Developing Selective Force.com Queries through the Query Resource Feedback Parameter Beta. Long story short, only your query filters (the WHERE clause) affect its cost. What style of outer join you use should have no impact. Note, however, that there is a maximum number of records you can retrieve with a Left Outer Join.

| improve this answer | |
  • What kind of limit does the LEFT OUTER JOIN impose? I don't see anything special on the total amount of records returned mentioned here: developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/… – elian Jan 23 '17 at 14:47
  • You start to get some errors or data loss over a certain threshold. I think it's a thousand children to a single parent, but I can't remember exactly and it's hard to search from my phone. – Adrian Larson♦ Jan 23 '17 at 14:50
  • @elian see for example opfocus.com/… – Adrian Larson♦ Jan 23 '17 at 15:19
  • I tried using the Query Plan option on the developer console with the following results: Nested SELECTs Leading Operation Type: TableScan Cost: 1.3 Notes: Not considering filter for optimization because unindexed. Table: Task__c Fields: ["IsDeleted"] Adding a WHERE clause lowered the cost to 0??? No nested SELECTs: Leading Operation Type: TableScan Cost: 3.6 Notes: Not considering filter for optimization because unindexed. Table: Task__c Fields: ["IsDeleted"] – elian Jan 30 '17 at 12:39
0

You can use child-to-parent query as child object having multiple parent objects. 

SELECT Id, Parent1__r.Id, Parent2__r.Id FROM Child__c

You can add fields you needed. But keep in mind

Keep the number of fields to be searched or queried to a minimum. Using a large number of fields leads to a large number of permutations, which can be difficult to tune.

Have a look at more on relationship queries limits.

| improve this answer | |
  • I know, I can, this is example_1 from my post, but it's a bit more inconvenient to use it in this case. That's why I'm asking if there are performance implications of using the nested SELECT. – elian Jan 23 '17 at 12:37

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.