Low performance of CMDT SOQL queries when including FieldDefinition fields

[mmablom]

While running integration tests for functionality that includes Rollups, I noticed that the SOQL query to retrieve Rollups was taking on average 1 second to complete. This is caused by the inclusion of Relationship fields of the type FieldDefinition in the query, which seems to require a dynamic Schema lookup similar to Describe calls. Comparing query times with or without these fields can be a 20x difference, as seen here:

CUMULATIVE_PROFILING | SOQL operations

AnonymousBlock: line 1, column 1: [
  SELECT Id, LookupFieldOnLookupObjectText__c
  FROM Rollup__mdt
  WHERE DeveloperName = 'WOLI_Part_Request_Expected_Date'
]: executed 1 time in 48 ms

AnonymousBlock: line 7, column 1: [
  SELECT Id, LookupFieldOnLookupObject__c
  FROM Rollup__mdt
  WHERE DeveloperName = 'WOLI_Part_Request_Expected_Date'
]: executed 1 time in 991 ms

I can think of two ways to optimize this in RollupRepository:

1. As discussed in SFXD; Add an option in Rollup Settings or Rollup Control to toggle querying of the FieldDefinition fields of. This would require all Rollups to use the {field}Text__c fields instead of the Relationship fields. In the RollupRepository the query can then strip out those fields.
2. Store Rollups including the FieldDefinition fields in the Org Cache and optionally refresh the cache based on the max LastModifiedDate of Rollups. This would (in theory) be be functionally the same for the end user and keep the nice features of FieldDefinitions, such as keeping the reference when a field is renamed. However, I'm not sure if the Org Cache is always available or if there are any other issues that might crop up.

I'd be interested in creating a PR to implement some optimization, let me know what has your preference or if there's any better solution.

[jamessimone]

@mmablom thanks for submitting, and yes, very interested in optimizing this. I've created https://github.com/jamessimone/apex-rollup/tree/v1.7.5 in the event that you're interested in creating a PR in the coming days for this!

[jamessimone]

@mmablom this was fixed (as well as it can be, currently) with #707. I am pushing teams internally to fix the underlying CMDT query performance

[mmablom]

@jamessimone thanks for the update, good to know! Apologies for not getting back to this, got caught up in projects.

[jamessimone]

@mmablom believe me, I hear that! Best of luck with your endeavors