Leverage Platform.Cache to speed up QueryBuilder SOQL creation

[rsoesemann]

I have a production system where the Selector classes and inside them the construction of complex QueryBuilder SOQL queries takes the most of the time.

I thought about using Platform Cache for that and storing the resulting query for later use.

1. Has anyone done and implemented that?
2. Which risks are involved and how can they be mitigated?

[daveespo]

(I converted this to a Discussion since it's not really an Issue)

In my day-job projects, we recently experimented with using Platform Cache to store the resolved SOQL. The primary challenge is coming up with a key for the cache. The key is a tuple that likely factors in the Selector name, the Selector Method, and a subset of the arguments to that method.

For example:

Selector_Contact.selectByPostalCode(String postalCode, Boolean includeAccount)

The cache key would likely need to be something like Selector_Contact.selectByPostalCode|true That is, you'd omit postalCode from the key because it's most likely a bind variable in local scope whereas the value of includeAccount produces the materially different SOQL string.

Another more difficult challenge is in Production, the use of Field Sets will materially change the SOQL that's generated. The Admin would need to be provided a facility to invalidate the cache any time they changed field sets on your objects.

In short, this is a challenge and not something easily handled at the the framework level, IMO. If you have alternative approaches, I'm interested in hearing them.

There are other more minor challenges .. i.e. developers would need to be very aware of the use of the cache and make sure to invalidate the cache any time they push changes that might affect the SOQL. But with some training or deciding to disable the cache in developer orgs, you can probably mitigate that one.

Separately, the AEP team has some loose ideas written down (privately for now, but will be shared around publicly soon enough) for supporting User Mode in Summer '22 as an optional alternative to the very high overhead way of enforcing CRUD and FLS in QueryFactory today. This native support for CRUD/FLS enforcement will likely obviate the need for the costly Describes that are used in QueryFactory.getFieldPath(). It's our hope and goal to reduce the CPU expense of using 'wide' selectors. Stay tuned on that one.

[bobalicious]

The fact that the QueryFactory performs the security checks is also a concern for caching. This may currently only be an issue for Org level cache, but future roadmap suggests that temporary elevation of privileges may become a thing in the near future too, causing difficulties for Session caching too.

[rsoesemann]

Thanks, @daveespo for basically saying "this is not a totally absurd idea, we also tried it". Most of your downsides are not so relevant in my case:

• We don't use Fieldsets
• Indeed we use a key like you describe
• Each Package upgrade calls a script which invalidates this cache
• We plan to use Org Cache as the security concerns @bobalicious mentions are not relevant. All our queries expect the user to have access and do NOT fail gracefully when fields are not available. I would bet most Apex coce out there is like that.

Indeed I hope for fflib simplifications with the new UserMode enhancements but they are in the far future.

[daveespo]

Just want to add that we found with one of our recent managed package upgrades that cache invalidation is subject to race conditions. About 2% of our subscriber org population was still holding onto cache entries that were from the prior version of the package.

We have no way to create a repro case, but the supposition is that the code in the Post Install handler that clears the cache entries "commits" while another transaction that started while the package was being upgraded is still in flight -- when that other execution context "commits", it wins and the old shape of the cache entry is re-added to platform cache

Our plan to circumvent this problem is to generate cache keys with salt based on the date that the package was last installed/upgraded.