Why is my SOQL query slow the first time, faster subsequently?

Question

I am trying to understand how Salesforce executes a SOQL query. I have a query and when i run it for the first time (using Workbench, Developer Console or from the page) it takes more than a minute to return the results. But running the query second time or as many times after that it returns the same results in milliseconds. What could be the cause of this behavior?

Answer 1

Short Answer: because its a RDMBS backing it.

Longer Answer: Your data is ultimately stored as blocks on some storage device somewhere, if not recently access the database will have to go read those blocks into memory before it can run the query, the larger the number of blocks that need to be scanned that aren't cached the longer this will take (note, this is related to all the data needed to execute the query, not the number of returned rows). Once read, these blocks will remain in memory in a db level cache. If you execute the same or similar query again shortly after, all that very expensive I/O can be skipped and the query executed against the already cached blocks.

Answer 2

This is documented in the query optimizer FAQ.

The key section is:

Q: I have a query (with multiple subqueries) that takes up to 40 seconds to execute the very first time its executes, regardless of the # of records returned. Any further query are quick. Is this possibly due to the fact the query is not known at compile time so it can't be optimized? I could try to rewrite the query with an explicit query. Or would it be better to break out subqueries into separate transactions?

A: Runtime varies as once most of the rows are in the db cache, the fetching time will decrease considerably. If the subqueries join large objects, you may want to split the query into multiple transactions. Additionally query cursor locators are something to consider when using subqueries.

The full FAQ is available at:

http://help.salesforce.com/apex/HTViewSolution?id=000181277&language=en_US

Answer 3

The query returns the results faster because they use the concept of "Database Caching".

Basic explanation:

One way to increase the performance of an application is to reduce the network overhead. To do so, a cache table is stored on the application server (usually) to hold the query results. By having a cache table, it gives the advantage of saving a round trip to the database server whenever the query results are found within it.