This is a question about your experiences managing huge amount of data for UIs.
With an ERD like this, where the arrow directions reflect the fields lookup directions. (e.g. entity "B" have a lookup field pointing to "C" and so on..)
I forgot to include the cardinality on the picture, let's suppose it is "N -> 1"
We have to show a data table with the following fields, where the letter + point + number indicates a random field for a given object (e.g. "A.1" is AnyField__c from A sObject, C.2 is "AnyOtherField__c" from C sObject...)
There is an average of 14 Million records per object.
Our options are:
SELECT lookup_To_E__c, lookup_To_E__r.field_1_c, lookup_To_E__r.field_2_c, lookup_To_E__r.field_3_c, lookup_To_E__r.lookup_To_C__r.field_1_c, lookup_To_E__r.lookup_To_C__r.field_1_c, FROM F WHERE ExternalId__c = 'SOMETHING'
Run 1st Query:
SELECT lookup_To_E__c FROM F WHERE ExternalId__c = 'SOMETHING'
Extract all the Ids from "lookup_To_E__c" with Apex into "filteredIDs"
Run 2nd Query:
SELECT lookup_To_C__c FROM E WHERE ExternalId__c IN :filteredIDs
Extract all the Ids from "lookup_To_E__c" with Apex into "moreFilteredIDs"
Run 3rd Query:
SELECT field_1_c FROM C WHERE ExternalId__c IN :moreFilteredIDs
- Which option is the better one?
- Is there any other option?
- What is the impact to include subqueries in the SELECT or WHERE clouses.
- Is it better to use this
formula fieldinstead ?
We are following all the Salesforce recomendations for indexing and selectivity: