Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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.

share|improve this answer

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.

share|improve this answer
    
I was trying to find the documentation of this, any idea where that's at ? –  Samuel De Rycke Jan 28 at 21:55
    
same here...is there any documentation available from salesforce which helps understand the execution plan –  Sahil Jan 28 at 21:57
    
No sorry, I don't have any link for it. –  ChopaForce Jan 28 at 22:01
    
This is the closest thing I can find: help.salesforce.com/apex/… –  Mark Masterson Jan 28 at 22:30
8  
query results are not cached at the app server tier. –  superfell Jan 28 at 23:55

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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