2

How long does Salesforce keep results of a query in its internal database. We know that the first execution of a query takes the longest. Subsequent queries are much faster due to the caching mechanism. Anyone knows how long is that cache valid for?

We have a case where we query the Account object which has huge amount of records. We would like to make sure that is always cached on the db side by rerunning it every now and then so that users do not get timeouts. I know it is probably SF internal question, but maybe someone has an idea how it works internally.

We were thinking how to optimize the SOQL query but it is quite hard since it is basically searching for accounts by Name with the LIKE clause and then ordering by name. Here is the query we would like to rerun on a scheduled basis: SELECT Id, Name FROM Account ORDER BY Name LIMIT 50.

Thanks!

Improve this question
2
  • Have you looked at the possibility of using Platform Cache for your use case? – David Reed♦ Mar 4 '20 at 13:43
  • Yes but we cannot hold the whole universe of accounts in Platform Cache – dream3n Mar 4 '20 at 15:11
3

There's no way to know for certain. Caches are designed to last as long as possible, but also to be evicted when they are invalidated (e.g. because a record changed) or when resources are required. There's also the added complexity of hardware and OS-level caches that also improve performance "randomly." These parameters are also tweaked on a fairly regular basis to optimize performance.

Salesforce.com does specifically recommend that you do not pre-warm the cache (I can't find a specific doc that states this, but I know I've heard it before). You should not try to take advantage of the cache specifically because it does get its performance altered from time to time, and optimizing a certain way today might cause a nightmare in the next patch or tweak.

Also, in regards to your specific case, the ORDER BY is on an indexed field, so you wouldn't gain any performance that way. One way you could help the database is, if you know what you're looking for, you could use SOSL instead to leverage the search index. Note that all indexes do not support a wildcard at the beginning, so you might also just want to make a separate field that splits the text in a way that allows the criteria to be met via indexes. Since it's a scheduled query, I presume you could probably make a custom field that contains the name shuffled around in a way that allows this optimization.

Indexes are better than cache, and more reliable. Figure out how to optimize the data within the indexes, and you'll have a solution for your timeout problem. SOSL requires that you know the first letter of the word you're searching, and SOQL requires that you know the first letter of the field in order to use indexes.

You can check if your query is optimized by using the Query tab in the Developer Console. Click on the Query Plan button after putting in your query, and you'll see what the database cost for your query is. You need to make sure its cost is low enough that the data can be found reliably without depending on the cache.

Improve this answer
4
  • 1
    Will skinny table be of any help in this case? – Samir Mar 4 '20 at 14:17
  • @Samir It will only help if they are using data that can be retrieved via index, which means that they'll still likely need to perform some data manipulation to get the data into a suitable format for the index. I would consider that a "step 2" after attempting to leverage indexes. – sfdcfox Mar 4 '20 at 14:47
  • Thanks for the elaborate answer. Still not sure how creating a custom index would help here. From what I understand Name field is indexed on Account by default and yet when we look at the query plan it is doing a full table scan, probably because of the LIKE clause which uses fuzzy text search on the Name field. – dream3n Mar 4 '20 at 15:24
  • @dream3n I was suggesting the opposite--attempting to manipulate the data so the match is no longer fuzzy, either by way of search indices, or by creating a field that has the data shuffled around in such a way that the first character matches the first character of your fuzzy match, thus being able to leverage an index. Creating a new index obviously won't help, because indexes need the first letter to match. – sfdcfox Mar 4 '20 at 15:35

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.