1

I'd like to do performance tests of the same SOQL query to check which order of WHERE and indexes configuration would be the fastest, but database caching makes it hard - after the first SOQL, the next ones are much faster, up to 1/4 of the original time.
I've read on the Salesforce page that direct cache flushing is not possible:

Q: Is there any way to flush the cache when doing your performance testing so your results are not cache biased?

A: Unfortunately not. Queries with selective filters will perform more consistently with less performance variation due to caching, however.
Source: https://help.salesforce.com/s/articleView?id=000330734&type=1

However, the cache size should be finite, so I tried to overwrite the current cache with another, not related query with large output, but the first query still was as fast as before.

Do you know any reliable way to flush the database cache, or maybe there is a different approach for query testing?

Improve this question

1 Answer 1

Reset to default
0

No, you cannot directly test query performance. Test performance would depend on the time of day, load balancing, sharing settings on the object, the user's profile permissions, the specific filters used, and so on. Even if you could clear the cache, the results would be nearly meaningless. However, you can use the Query Plan Tool to determine what the relative cost of a SOQL query will cost. This should give you a relative idea of how well your query should perform on average, with the lower cost meaning better performance.

Edit: I just also realized that you're asking about the ordering of filters. The Query Plan Tool tells you the cost of each filter. The system automatically uses the best estimated cost for filters, so your manually changing filters around should have little to no impact anyways, because the order in which you specify filters is not necessarily the order in which they will be applied.

Improve this answer
4
  • 1
    Sadly we have historically found reordering WHERE clauses can significantly impact performance. It shouldn't but can.
    – Phil W
    Apr 21, 2022 at 18:44
  • @PhilW that's why I said "should." I have run into it before, but it is kind of rare. Salesforce "usually" gets it right, which is why I'd just not worrying about it unless it goes wrong, and if it does, that's usually a support ticket.
    – sfdcfox
    Apr 21, 2022 at 18:47
  • I was taught (in Database course, outside of SF) that the order of conditions in WHERE matters and it's better to have more strict conditions at the start, so the next ones are compared against the fewer records - honestly I don't feel happy knowing Salesforce tries to do it for me.
    – zanstaszek9
    Apr 21, 2022 at 19:44
  • @zanstaszek9 Hmm. I've never heard of a database system that executes the filters exactly in the order you specify by default. MySQL, SQL Server, PostgreSQL, Oracle, Salesforce--they all have a Query Plan Optimizer that's intended to find the most optimal solution for you. It's rare to need to do this yourself, and usually the salesforce.com engineers do a good job of keeping the system optimized so you don't have to. Your only real choice is if you ask for custom indexes or skinny tables.
    – sfdcfox
    Apr 21, 2022 at 20:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .