+ Start a Discussion
ajay ambatiajay ambati 

error while retriveing 100000 records in vfpage

Retrieving more than 100.000 records using select statement in visual force how can it possible?pls help me with exact soql query?  but soql support only 50000 rows please hlp me.
Best Answer chosen by ajay ambati
G10G10
Yep Ajay,

That is the only feasible solution to get more than 50K records, but you will not be able to perform DML operations when you execute a transaction.

Hope this is helpful to you :)
Happy coding.

Regards,
Jiten.

If a reply to a post answers your question or resolves your problem, please mark it as the solution to the post so that others may benefit.

All Answers

FearNoneFearNone
you can use queryMore
boolean done = false;
qResult = connection.query(soqlQuery);

while (!done) {
	SObject[] records = qResult.getRecords();
	for (int i = 0; i < records.length; ++i) {
	   Object rec = (Object) records[i];
	   System.Debug(rec);
	}
	if (qResult.isDone()) {
	   done = true;
	} else {
	   qResult = connection.queryMore(qResult.getQueryLocator());
	}
 }
https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_calls_querymore.htm
 
ajay ambatiajay ambati
But governerlimit fires max is 50000 records and I am saying in vfpage
G10G10
Hi Ajay,
 
Instead of getting all the records, how about using LIKE in your where clause?   
In addition, in terms of Best Practices for Data, I would strongly urge that you should always limit your query with where clause, this will indirectly help in system performance and efficiency (In your case it will help for avoiding a view state error and your page will be more responsive).
 
But for some of the requirements we still needs more than 50K records, so there are two ways to achieve it however both has their own limitation.
  • @ReadOnly annotation was released which allows you to query up to 1 Million SOQL Rows. However, this annotation has some major caveats. Most notably, you will not be able to perform DML operations when you execute a transaction.
           Take a look at the documentation here:
           https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_annotation_ReadOnly.htm
           https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_controller_readonly_context_methodlevel.htm
  • You should look at using Batch Apex to accomplish your goals. Retrieving more than 50,000 records your SOQL calls in a single context isn't possible. 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.
           Check the Documentation here :  
           http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm

Second approach is feasible only if you want to perform any asynchronous transaction. 
Hope this is helpful to you :)
hApPy CoDinG :)

Jiten.
 
ajay ambatiajay ambati
second way you sayng in batch apex but my requirement in visualforce page. and my idea is to go for the pagination with stndardsetcontroller ist possible or not pls say me.
G10G10
Hi Ajay,

NO, In you requirment you cant use stndardsetcontroller for working over 100000 records.
The maximum record limit for StandardSetController is 10,000 records. Instantiating StandardSetController using a query locator returning more than 10,000 records causes a LimitException to be thrown.

Check the Documentation here :  
https://developer.salesforce.com/page/Paginating_Data_for_Force.com_Applications (https://developer.salesforce.com/docs/atlas.en-us.pages.meta/apexcode/apex_pages_standardsetcontroller.htm)
https://developer.salesforce.com/docs/atlas.en-us.pages.meta/apexcode/apex_pages_standardsetcontroller.htm


hApPy CoDinG :)

Jiten.
 
ajay ambatiajay ambati
k fine only the way is use @readonly annotation in controller of vfpage. is it right
G10G10
Yep Ajay,

That is the only feasible solution to get more than 50K records, but you will not be able to perform DML operations when you execute a transaction.

Hope this is helpful to you :)
Happy coding.

Regards,
Jiten.

If a reply to a post answers your question or resolves your problem, please mark it as the solution to the post so that others may benefit.
This was selected as the best answer
Hari hara suthanHari hara suthan
Hi Ajay,

Why don't you try javaScript remoting approach? You can able to get large number of records as a JSON response. Refer this https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_js_remoting_example.htm?search_text=remote.

Thanks,
Hari