+ Start a Discussion
laurioulauriou 

Updating 100,000 records

Hi

I have a custom field in a custom object I need to update to a same value. The issue is that there are 100,000 records in this custom object and of course I will hit the governor limit to 10,000 records. I just want to create a button which will call an Apex Class for these 100,000 updates!

Has anyone had this problem and found a solution for it?

any ideas?

Thanks for your help


DManelskiDManelski
I think your best option may be a Visualforce page plus an actionPoller.  Do a search for actionpoller for more information/ideas.
JimRaeJimRae
If it is a one time update, I would use the data loader. It will batch the updates for you and handle this type of volume with no problem.
laurioulauriou
Unfortunately, this would need to be done every day. I think the design need to be reviewed!
Thanks for your help
JimRaeJimRae
Yes, you might want to reconsider the requirement.  Maybe you could use a formula field for this field that needs to be updated, and calculate the new value based on the date and/or whatever other parameters might be necessary to derive the value?
Then, when you accessed the object, in the UI or even in a report, the value should be correct.
laurioulauriou
For readers, I managed to have it working by using VF actionPoller, thanks DManelski

here is the VF code that call MassCurrentDateUpdate Apex which update 100 records every 5 seconds:

<apex:page controller="MassCurrentDateUpdate">
<apex:form >
<apex:sectionHeader title="Updating all Sales Results records with today date for the Current Date Field - Wait for the process to be completed before changing page"></apex:sectionHeader>
<apex:pageMessages />
<apex:outputText value="This Process update the CurrentDate fields with Today date"/><br></br><br></br>
<!-- <apex:outputText value="Batch Counter: {!count}" id="counter"/><br></br><br></br>-->
<b><apex:outputText value="Process: {!EndProcess}" id="EndProcessId"/></b>
<apex:actionStatus startText="(Processing...)" stopText="..." id="counterStatus"/>
<apex:actionPoller action="{!incrementCounter}" interval="5" rerender="EndProcessId" status="counterStatus"/>
</apex:form>
</apex:page>

it is still a manual process as you have to open this page, but at least I update the 100,000 records

Cheers

Serge
DManelskiDManelski
Hi Serge,

Is there any way that you could post the code for your controller as well?

Thanks,
Dave
Guyver118Guyver118
Hi I would also like to see how you achieved this. Could you post your apex code :)
laurioulauriou

Here you go:

 

// update Current_date__c to today() in sales_Result__c
public class MassCurrentDateUpdate{
    Integer count = 1;
    Integer srRowStart = 1;
    Integer srRowStop = 0;   
    Integer Process = 1;
    integer sri = 1;
    List<SalesResults__c> lastSrs = [select Name from SalesResults__c order by CreatedDate desc limit 1];
    String lastSr = lastSrs[0].Name;
    String lastSri = lastSr.substring(9);
    Integer intLastSri = integer.valueOf(lastSri)+1;
    String EndProcess = 'Process is starting soon... do not change page - Number of records to update: '+intLastSri;
    decimal PercentDone = 0;
   
    public PageReference incrementCounter() {
        if (sri < intLastSri) {
            srRowStop = srRowStart + 100;
            for ( sri = srRowStart ; sri < srRowStop ; sri++){
                String Nname='________-'+sri;
                List<SalesResults__c> salesResults = [select CurrentDate__c, Name from SalesResults__c where Name Like :Nname];
                Process = salesResults.size();
                if (Process != 0){           
                    for (SalesResults__c sr : salesResults) {
                           sr.CurrentDate__c = system.today();
                       }
                    try{
                        update salesResults;
                    }
                    catch (DmlException e) {
                        ApexPages.addMessages(e);
                    }
                }
            }
            srRowStart =(count*100)+1;
            count++;
            PercentDone = (srRowStop*100) / intLastSri;
            EndProcess = 'Processing... Do not change page - Records Processed: '+sri+' ( '+PercentDone+'% done )';
        } else {
            EndProcess = ' PROCESS COMPLETED... All Sales Result records have now the Current Date of today, you can go now to an other page';   
        }   
       
   
    return null;
    }
   
    public String getEndProcess() {
        return EndProcess;
    }
}

--------------

Serge

Guyver118Guyver118
Hi, can you quickly explain how your splitting the records for the batch update, are you going by name and doing them alphabetically?
DManelskiDManelski

Much appreciated Serge, this was really helpful for me in developing an actionpoller VF page of my own.

 

Cheers!

laurioulauriou

I am using the autonumber ({YYYY}{MM}{DD}-{0}), so just iterate all of these records 100 by 100 using the String Nname='________-'+sri;

Fortunately it is easy with autonumber, if you have a name, I am not sure how you can do that

Cheers

Serge

 

Guyver118Guyver118

i think the best solution is to use javascript i done something like this, which works really well. The problem is that list or any of the collections have the slice method: Note i have limited it 20k records but it will update all records if needed. :)

 

 

 

<html>
<head>
<script src="/soap/ajax/14.0/connection.js"></script>
<script type="text/javascript" src="/js/functions.js"></script>
<script>
function Setup()
{
var accList = new Array();
var batchList = new Array();

var strQuery = "SELECT Id, Name FROM Account LIMIT 20000";
var resultQuery = sforce.connection.query(strQuery);
var queryMore = true;

while(queryMore)
{
var records = resultQuery.getArray("records");

for(var i = 0; i < records.length; i++)
{
var a = new sforce.SObject("Account");
a.Id = records[i].Id;
a.OwnerId = '005S0000000DyOm';
a.Batch_Status__c = "AA UPDATE";
accList.push(a);
}

if(resultQuery.getBoolean("done"))
{
queryMore = false;
}
else
{
resultQuery = sforce.connection.queryMore(resultQuery.queryLocator);
}
}

var maxRecords = accList.length;
var subIndex = 0;
var successes=0, errors=0;
var percent = 0;

alert('**** RECORDS TO UPDATE: ' + maxRecords + ' ****');
alert('**** STARTING UPDATE ****');

while(subIndex <= maxRecords)
{
batchList = accList.slice(subIndex, Math.min(subIndex+200, accList.length));
var results = sforce.connection.update(batchList);

while(results.length)
{
if(results.pop().getBoolean('success'))
{
successes++;
}
else
{
errors++;
}
}

percent = (subIndex*100)/maxRecords;
GetPercentage(percent);
subIndex += 200;
}

alert('**** RESULT: SUCCESSES: ' + successes + ' ERRORS: ' + errors + ' ****');
}

function GetPercentage(percent)
{
var holder = document.getElementById("resultHolder");
holder.innerHTML = "<b>PERCENT: " + percent + "</b>";
}
//window.setInterval("GetPercentage();", 1*1000);
</script>
</head>
<body onload="Setup();"> YIPEEEE
<div id="resultHolder"></div>
</body>
</html>

 

 

 

Message Edited by Guyver118 on 02-05-2009 01:53 AM