+ Start a Discussion
Bailey Carlson (CRMCulture)Bailey Carlson (CRMCulture) 

QueryLocator ignoring limit clause

Can anyone explain this behavior. I'm seeing a very significant performance difference between the two methods of writing a query in APEX,
 
List<sObject> contacts = [select Name from Contact limit 100]; // Static SOQL
Database.QueryLocator query = Database.getQueryLocator('select Name from Contact limit 100'); // Dynamic SOQL
Running profiler on the two lines of code in the Developer console reveals that the second query takes many orders of magnitude longer to run but returns only one row. It also indicates that the SOQL without the limit is being executed although only the limited number of rows are returned.

16:20:52.026 (26387500)|SOQL_EXECUTE_BEGIN|[1]|Aggregations:0|select Name from Contact limit 100
16:20:52.026 (26398954)|LIMIT_USAGE|[1]|SOQL|1|100
16:20:52.026 (26403155)|LIMIT_USAGE|[1]|AGGS|0|300
16:20:52.034 (34778605)|SOQL_EXECUTE_END|[1]|Rows:100
16:20:52.034 (34788820)|LIMIT_USAGE|[1]|SOQL_ROWS|100|50000
AnonymousBlock: line 1, column 1: [select Name from Contact limit 100]: executed 1 time in 9 ms

vs.

16:20:52.037 (37114365)|SOQL_EXECUTE_BEGIN|[2]|Aggregations:0|select Name from Contact 
16:20:52.037 (37123249)|LIMIT_USAGE|[2]|SOQL|2|100
16:20:52.037 (37127604)|LIMIT_USAGE|[2]|AGGS|0|300
16:20:52.294 (294504152)|SOQL_EXECUTE_END|[2]|Rows:100
16:20:52.294 (294522076)|LIMIT_USAGE|[2]|QUERY_LOCATOR_SOQL_ROWS|100|10000
AnonymousBlock: line 2, column 1: Database.getQueryLocator(String): executed 1 time in 258 ms

Notice the differences between the query are logged with the limit vs without the limit keyword and the difference in query times. It alarms me to think that the QueryLocator may be running the query without any limit in all cases! I prefer to use the QueryLocator method because I would like to pass it to the StandardSetController so that it can handle paging for me (it is limited to 10000 records whereas limit/offset cannot go beyond 2000). Note that the QueryLocator is still more performant than running the query without any limit so I don't really have an explaination for what is happening. If anyone has any insight on this I would greatly appreciate.


ShashankShashank (Salesforce Developers) 
If it happens everytime irrespective of resource availability (since it is multi-tenant), the only reason I can think of is that dynamic soql needs to be consructed explicitly whereas statis soql is already a constructed query.