+ Start a Discussion
imAkashGargimAkashGarg 

Fetch more than 50000 records by SOQL

if I have more than 50000 orders (custom object) in salesforce. Then, wirting the code:

 

List<Order__c> ord_list = [Select id, source__c from Order__c];

 

would end up in governor limits. As the maximum number of records taht can be fetched by a SOQL is 50000.

What is the best way to do this?

(i have to get all the records in a single variable)

 

Thanks.

Best Answer chosen by Admin (Salesforce Developers) 
Cory CowgillCory Cowgill

You should look at using Batch Apex to accomplish your goals.

 

You cannot retrieve more than 50,000 records your SOQL calls in a single context.

 

However, with  Batch Apex your logic will be processed in chunks of anywhere from 1 to 200 records in a batch.

 

You'd need to modify your business logic to take the batching into account if necessary.

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm

All Answers

Cory CowgillCory Cowgill

You should look at using Batch Apex to accomplish your goals.

 

You cannot retrieve more than 50,000 records your SOQL calls in a single context.

 

However, with  Batch Apex your logic will be processed in chunks of anywhere from 1 to 200 records in a batch.

 

You'd need to modify your business logic to take the batching into account if necessary.

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm

This was selected as the best answer
bob_buzzardbob_buzzard

There is also the option of the @ReadOnly attribute in Winter 12 - if your code fits the criteria (scheduled, webservice or VF controller method) you can retrieve up to 1 million rows, but you won't be able to make any changes to them.

imAkashGargimAkashGarg

Thanks for the suggestion.

 

Well, i have to perform DML operation on the list.

What will be the best option to do this?

bob_buzzardbob_buzzard

As Cory said above, batch apex is the only way to access more than 50k records.

imAkashGargimAkashGarg

Thanks.

fcomdeveloperfcomdeveloper

We can fetch more than 50000 records but how can we process all of them at once since I have a requirement to calculate some averages which I cant do using a aggregate query as it is more than 50000 records.

 

How can I store in a list which doesnt get refreshed each time the batch job is run??

Cory CowgillCory Cowgill

Recently the @ReadOnly annotation was released which allows you to query up to 1 Million SOQL Rows.

 

However, this annotation has some major caveates.

 

Most notably, you will not be able to perform DML operations when you execute a transaction.

 

Take a look at the documenation here:

 

http://www.salesforce.com/us/developer/docs/pages/index_Left.htm#StartTopic=Content/pages_controller_readonly_context.htm

Cory CowgillCory Cowgill

I see Bob Buzzard already mentioned the @ReadOnly earlier. Sorry for reposting info. :)

bob_buzzardbob_buzzard

This is scheduled apex, not batch apex - batch breaks down the processing into blocks of max 200 records.  Scheduled is executed in a single transaction and is still limited to 50000 records.

fcomdeveloperfcomdeveloper

thats the reson I used @ReadOnly annotation. The problem with batch job is I have to calculate averages for all the records which will be for sure over 50000 record which I can't do using an aggregate query.

bob_buzzardbob_buzzard

My apologies - I missed the @readonly annotation.

 

Are you seeing exactly the same error, or are you hitting limits around the maximum number of elements that can be stored in a list?

bob_buzzardbob_buzzard

Also - are you executing this via the scheduler, or are you hitting the execute method directly?

fcomdeveloperfcomdeveloper

I am doing it with the scheduler

bob_buzzardbob_buzzard

And you get an error that the maximum number of soql records have been retrieved?

fcomdeveloperfcomdeveloper

Now i don't get that error but  I exceeded the heap limit . I don't know what to do now

 

Apex heap size too large: 7772813

 

bob_buzzardbob_buzzard

That's a different issue - the only limit relaxed by the @readonly annotation is the number of records that can be returned by a query.  Have you tried using a list based SOQL for loop, as described in the following link:

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_loops_for_SOQL.htm

 

 

fcomdeveloperfcomdeveloper

Now that it looks like there are no exception that the debug logs show its not kicking off the batch job that am calling at the end of this class. I can't even see the last System.debug I put to see the full map at the end of the process.

 

}

 

I don't know why it is not starting the batch job but the debug logs doesnt show any exceptions with reference to governor limits

 

The last few lines of the log

 

 
bob_buzzardbob_buzzard

I'd add a try/catch block around the iteration of the POE_Summary__c records - I can't see why an exception wouldn't make it to the debug log, but that looks to be where its giving up.

 

I'd also add some debug all the way through in case its elsewhere.

nagalakshminagalakshmi

Hi,

 

how to insert morethan 50000 records through coding. i am able to insert the records up to 50000 successfully using batch apex class. But if i am having morethan 50000 records its getting an error as 'Too many query rows: 50001'. Please help me how to solve this and how to insert morethan 50000 records.

 

Thanks,

Lakshmi


Too many query rows: 50001
nagalakshminagalakshmi

Hi,

 

I need to delete the records more than 50000 records from the object and need to compare with parent id. how can i compare the parent id while passing the query in batch apex class.

 

string query='select id,store__c,Product_category__C from StoreProduct_category__C where store__C in :setsid';

MassDeleteRecords batchApex = new MassDeleteRecords(query);
Database.executeBatch(batchApex,10000);

 

How can i use the where condition in query. If used like this i am getting error as 'First error: Variable does not exist: setsid'. Please help me out.

 

Thanks

Nishant sinhaNishant sinha

You can use Batch apex to get the records, and keep a variable which maintains the state (using Database.Stateful).

You can use this variable to keep on calculating the average of records in each batch.

 

Let me know if you need some code solution for this.

Database.Stateful