5

Background

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"

enter image description here

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...)

enter image description here

There is an average of 14 Million records per object.

Our options are:

Option 1:

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'

Option 2:

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

Questions

  1. Which option is the better one?
  2. Is there any other option?
  3. What is the impact to include subqueries in the SELECT or WHERE clouses.
  4. Is it better to use this lookup_To_E__r.lookup_To_C__r.field_1_c or a formula field instead ?

Notes:

We are following all the Salesforce recomendations for indexing and selectivity:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.