function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
RajanJasujaRajanJasuja 

How to select more than 10000 records from an object using SOQL

Hi,

 

As you all know Salesforce is phasing out s-control next year, so I am given the task to convert one functionality written in S-control to VF page.

 

My object is select more then 10000 records from the object (lets say my object 30000 records). Even consider by applying all filter criteria SOQL still need to fetch more than 10000 rows.

 

But when I try to select more then 10000 records using SOQL, apex throw a limit exception to me.

So any idea how we can select more then 10000 records in VF/Apex solution.

 

Any suggestion is appreciable.

 

Thanks

Rajan

Best Answer chosen by Admin (Salesforce Developers) 
SatgurSatgur

Try using VF actionPoller and create a numeric Id custom field on your object. Actually you need to create 2 custom fields on the object. One Auto-number field which is of text data type by default, and second field of formula type(e.g. SEQ_ID) which will copy sequential numeric value from the Auto-number text field.

 

Use actionPoller to call one method in the controller, which will query and filter records from the object based on the numeric value of formula field.

Keep track of the last number of records fetched in the previous call by actionPoller. Also keep incrementing getting the next set of records.

By each actionPoller call you can fetch 10000 records. Each actionPoller call executes in different execution context.

 

Below is some sample code for your reference, I hope this will work for you.

 

VisualForce page code:

<apex:page controller="VF_Contolroller">
    <apex:form >
            <apex:outputText value="Counter: {!count} |SelectRecordCount: {!selectRecordCount}" id="counter"/>
            <apex:actionStatus startText="Applying Value..." id="status" />               
            <apex:actionPoller interval="5" action="{!selectMaxAccounts}" rerender="counter" status="status" enabled="{!running}"/>
    </apex:form>
</apex:page>
 
 Apex Controller code:
global class VF_Contolroller {
    
    public Integer count = 0;
    public Integer selectRecordCount=0;
    public Integer maxSelectRecord=10000;
    public Boolean running=true;   
    
    public PageReference selectMaxAccounts()
    {
	try
	{
		List<Account> lstAcc = new List<Account>();
		running=false;
		for(Account[] accts: [Select Id, Name from Account where SEQ_ID  > :selectRecordCount and SEQ_ID <= :maxSelectRecord])
		{
			if(notes.size() > 0)
			running=true;
			for(Account a : accts)
			{
				count++;          
				if(lstAcc.size() == 1000)
				{   update lstAcc;
				    lstAcc.clear();
				}
				lstAcc.add(a);
			} // End For-Loop accts
		} // end of for loop of SQL LIST query

		selectRecordCount=maxSelectRecord;
		maxSelectRecord=maxSelectRecord+10000;
		if(lstAcc.size()>0) update lstAcc;                                
	}
	catch(System.Exception Ex){
		System.debug('EXCEPTION OCCURED = ' + Ex.getMessage());
	}
        return null;
    }
    
    public Integer getCount() {
        return count;
    }
    
    public Integer getSelectRecordCount() {
        return selectRecordCount;
    }
    
    public Boolean getRunning(){
        return running;
    }
}
 
Thanks,
Satgur 
 


All Answers
SatgurSatgur
Try using VF actionPoller
and create a numeric Id custom field on your object. Actually you need to
create 2 custom fields on the object. One Auto-number field which is of text
data type by default, and second field of formula type(e.g. SEQ_ID) which will
copy sequential numeric value from the Auto-number text field.



 



Use actionPoller to call
one method in the controller, which will query and filter records from the
object based on the numeric value of formula field.



Keep track of the last
number of records fetched in the previous call by actionPoller. Also keep
incrementing getting the next set of records.



By each actionPoller call
you can fetch 10000 records. Each actionPoller call executes in different
execution context.



 



Below is some sample code
for your reference, I hope this will work for you.
 
VisualForce page code:



<apex:page controller="VF_Contolroller">
    <apex:form >
            <apex:outputText value="Counter: {!count} |SelectRecordCount: {!selectRecordCount}" id="counter"/>
            <apex:actionStatus startText="Applying Value..." id="status" />               
            <apex:actionPoller interval="5" action="{!selectMaxAccounts}" rerender="counter" status="status" enabled="{!running}"/>
    </apex:form>
</apex:page>
 
 Apex Controller code:
global class VF_Contolroller {
    
    public Integer count = 0;
    public Integer selectRecordCount=0;
    public Integer maxSelectRecord=10000;
    public Boolean running=true;   
    
    public PageReference selectMaxAccounts()
    {
	try
	{
		List<Account> lstAcc = new List<Account>();
		running=false;
		for(Account[] accts: [Select Id, Name from Account where SEQ_ID  > :selectRecordCount and SEQ_ID <= :maxSelectRecord])
		{
			if(notes.size() > 0)
			running=true;
			for(Account a : accts)
			{
				count++;          
				if(lstAcc.size() == 1000)
				{   update lstAcc;
				    lstAcc.clear();
				}
				lstAcc.add(a);
			} // End For-Loop accts
		} // end of for loop of SQL LIST query

		selectRecordCount=maxSelectRecord;
		maxSelectRecord=maxSelectRecord+10000;
		if(lstAcc.size()>0) update lstAcc;                                
	}
	catch(System.Exception Ex){
		System.debug('EXCEPTION OCCURED = ' + Ex.getMessage());
	}
        return null;
    }
    
    public Integer getCount() {
        return count;
    }
    
    public Integer getSelectRecordCount() {
        return selectRecordCount;
    }
    
    public Boolean getRunning(){
        return running;
    }
}
 
Thanks,
Satgur 
 
This was selected as the best answer
wesnoltewesnolte
Hey
 
You can use SOQL for loops to get around this. Documentation can be found here
 
http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content/langCon_apex_loops_for_SOQL.htm?SearchType=Stem
http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content/langCon_apex_SOQL_VLSQ.htm?SearchType=Stem 
 
Cheers,
Wes 


RajanJasujaRajanJasuja
Thanks for your reply, I try both the approaches.

When we use approach two, it works fine only if the total numbers of records selected are less then 10000, showing an Apex governor limit exception when we try to select more then 10000 records.

So I am fine with the approach one, it is working fine even, if the total numbers of records are more then 10000 and I think we can use this approach to update more then 1000 records because action Pooler changes the execution context after every interval.

 

Thanks, 
Rajan 



MachhiMachhi
Hi, 
 
I wonder how you could update records up to 10000. I am using second approach. 
 
I can loop through 3500 records but I can update only 800 records using the record list object. Below is the code snippet:  
 
Datetime strStartDate;
Datetime strEndDate;
Integer BATCH_TO_PROCESS = 10;

DateTime t = DateTime.now();
integer year = t.year();
strStartDate =  Datetime.ValueOf(year + '-01-01 00:00:00');
strEndDate =  Datetime.ValueOf(year + '-12-31 23:59:59');
Set<Id> ContactIdSet = new Set<Id>();

for(List<Contact> ContactList : [select Number_of_Cases__c, Number_of_Contracts__c, ID from Contact
where createddate >= :strStartDate and createddate <= :strEndDate])
{    
    for(Contact objContact : ContactList)
    {
        ContactIdSet.add(objContact.id);                
    }

    if(intBatchCount<BATCH_TO_PROCESS)
    {
        //This is asynchronous method which processes updates
        BatchContactUpdate(ContactIdSet);
        ContactIdSet.clear(); 
        
        //Increase batch count
        intBatchCount++;
    }
}

@future
public static void BatchContactUpdate(Set<Id> pm_ContactIds)
{
    List<Contact> contactsToUpdate = new List<Contact>{};
    List<Contact> contacts = [select Id, Number_of_Cases__c, Number_of_Contracts__c from Contact where id IN :pm_ContactIds];

    for(Contact c: contacts)
    {
        intCountForContact ++;
        if(pm_bIsIncludeNumberOfCases)
            c.Number_of_Cases__c = [select count() from Case where Contact.id = :c.id];     

        if(pm_bIsIncludeNumberOfContracts)
        c.Number_of_Contracts__c = [select count() from Contract where CustomerSigned.id = :c.id 
            or Contract_Owner__c = :c.id];

        //Add to contact update list 
        contactsToUpdate.add(c);
    }

    //Update all records together
    update contactsToUpdate;

}
 
 
What wrong am I doing here? I have a Visualforce page with custom controller containing above code which is being called on click of the "Run" button.                                  
It throws error as "Too many SOQL queries:101" error if the contact records in the current year are more than 900 on average.  
What I can't figure out is why I am getting limitation error of 100 SOQL queries even though I am using future annotation. If I do not use future annotation, I can process only 100 records. 
Any suggestion on how to overcome this situation?   (I am using approach suggested here: http://wiki.developerforce.com/index.php/Apex_Code_Best_PracticesI am afraid that this approach is limited only for calls inside trigger. )  
 
Thanks, 
Chandrakant M 
machhi_c_m@yahoo.com


Suzan2009Suzan2009
hi ,
 
i got same problem to query over 10k records.
your solution looks fine. 
My question is when more than 10 users working on this at same time, each user's interface(vf page) will update the records. Does it cause confilict? Because multi-users may update records at same time.
 
 
Thanks !!!!


cmd loadercmd loader
Hi Machhi,
 
how can i insert morethan 10000 records in to salesforce object. I have added these 10000 records in to list. How can i pass this list in to future class and how can i insert these values. Please help me out.
 
Thanks


ArunaAruna
Hi,


 


Can I use same approch to update more than 10000 records like 15000 records  onaccount,contact and lead.


 


I have a button on my page how can i apply this action pollar on to my button.


 


could you please suggest me , please help me on this.


 


Thanks


ArunaAruna
Hi, I am trying to  use your code ,


 


what is the notes.size() ,


if i need to update some value on account how can i do that


 


like i need to update Ownerid and other fileds.


 


Thanks 


Aruna


nagalakshminagalakshmi
hi Aruna,
 
use batch apex class, you are able to insert more than 10000 records easily.
1.write one batch apex class
2. And write one normal class and write one method and call that batch apex class in this method foe execution. And assign this method to button of vf page and try execute this one.
 
Thanks,
Lakshmi.