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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
This is documented in the query optimizer FAQ.
The key section is:
The full FAQ is available at:
The query returns the results faster because they use the concept of "Database Caching".
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.