Apex Code Development You need to sign in to do that
Don't have an account?
Elian Kurtenkov SOQL JOIN performance
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:
or
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 case 2?
Any reason to prefer option 1 over option 2 or vice versa?
P.S.
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"]
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 case 2?
Any reason to prefer option 1 over option 2 or vice versa?
P.S.
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"]