+ Start a Discussion
dmchengdmcheng 

Query locator 10K limit and workaround?

It seems the query locator object is still limited to 10K rows in standard Apex (not Batch) even though the total SQL query limit is currently 50K.  Is that correct?  If so, it's not documented anywhere.

 

If I need to add more than 10K rows to a StandardSetController, is there way around this?  Here's my non-working code now:

String qry = 'select Id, Name from EF_Participant__c WHERE Status__c != \'Inactive\' order by Last_Name__c limit 25000';
ApexPages.StandardSetController setConParticipant = new ApexPages.StandardSetController(Database.getQueryLocator(qry));

 

If I try to instantiate a List instead, then I get an Internal System Error

EF_Participant__c[] x = [select Id, Name from EF_Participant__c WHERE Status__c != 'Inactive' order by Last_Name__c limit 25000];
ApexPages.StandardSetController setConParticipant = new ApexPages.StandardSetController(x);

 

Best Answer chosen by Admin (Salesforce Developers) 
EJWEJW

For queries with large results sets use a SOQL for loop.  It mimics the behavoir of queryMore() from the WSAPI.  For example:

 

List<Account> accounts = new List<Account>();
for ( Account currAccount : [SELECT Id, Name FROM Account] )
    accounts.add( currAccount );

 

That will allow you to exceed the result limit of a direct assignment SOQL query, up to the governor limit.  However, you'll need to watch your heap size for larger results sets as well as you could run into the governor limit for memory usage.

 

See here for more details about working with large result sets: http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

All Answers

EJWEJW

For queries with large results sets use a SOQL for loop.  It mimics the behavoir of queryMore() from the WSAPI.  For example:

 

List<Account> accounts = new List<Account>();
for ( Account currAccount : [SELECT Id, Name FROM Account] )
    accounts.add( currAccount );

 

That will allow you to exceed the result limit of a direct assignment SOQL query, up to the governor limit.  However, you'll need to watch your heap size for larger results sets as well as you could run into the governor limit for memory usage.

 

See here for more details about working with large result sets: http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

This was selected as the best answer
EJWEJW

You can also use a SOQL for loop to retrieve the results in chunks rather than one at a time like this:

 

List<Account> accounts = new List<Account>();
for ( List<Account> accountBatch : [SELECT Id, Name FROM Account] )
    accounts.addAll( accountBatch );

 I'm not sure what the default batch size is, it's either 200, 500 or 2000 and I don't believe there's a way to change that.  Doing it this way will save you a lot of script statements if all you're wanting to do is pull them back into a list for use elsewhere in your code.

 

However, you might want to evaluate why you want that many records pulled back in a VF page controller anyway.  If you're aggregating the results (summing, averaging, etc..) you should look at performing an agregate query instead.  If you wanting to use this for manual paging then perhaps you should wait a week or two for the Spring '12 release where they're adding OFFSET for queries so you can return just the specific set of results from your query that you wish to display.

dmchengdmcheng

Ah of course.  I completely forgot about that, thanks for pointing out both.  I'm doing this as more of an academic exercise since the original code is in a VF Controller that uses "with sharing" and most of the use cases involve portal users who don't own more than a few hundred records.  A Salesforce sys admin user will get back all the records but is not supposed to be doing this particular task anyway.