Bulk update! how to update 500k record to cancelled.

[Community Alums]

Hi community,

I have a requirement to update 5 lakhs old resource plan record to cancelled, I tried doing with backgroud scipt but the issue is it only updates 20000/30000 records at a time and it stops. Is there any other way to do that. below is my script.

var grRP = new GlideRecord('resource_plan');
grRP.addEncodedQuery("end_date<=javascript:gs.dateGenerate('2021-12-31','start')^state!=8");
grRP.query();
while (grRP.next()) {
grRP.state = '8';
grRP.update();
}

Appriciate any help!

Regards,

Rafmine

[chrisperry]

Hi there,

I would try a few things:

1. Instead of running this script from background script, try using a Fix script instead and run it in the background

2. Set workflow to false to avoid triggering business rules and minimize resources needed to execute the script as below

3. Use the GlideRecord updateMultiple() method to improve performance as below:

var grRP = new GlideRecord('resource_plan');
grRP.addEncodedQuery("end_date<=javascript:gs.dateGenerate('2021-12-31','start')^state!=8");
grRP.query();
grRP.setWorkflow(false);
grRP.setValue('state', 8);
grRP.updateMultiple();

If this answer is helpful please mark correct and helpful!

Regards,

Christopher Perry

[Sourabh26]

Hi,

Instead of doing it from the background script you can go for the fix script. Although i would suggest to do it from the scheduled job (which should run post business hours) so that no performance impact would occurs during business hours.

Also use the setWorkflow(false) so that no other BR will trigger with the changes. 

As you are updating the records through script so I would also suggest to include autoSysFields(false) so that there will not be entries in the updated by for your name.

Use below script for the same -

var grRP = new GlideRecord('resource_plan');
grRP.addEncodedQuery("end_date<=javascript:gs.dateGenerate('2021-12-31','start')^state!=8");
grRP.query();
grRP.state = '8';
grRP.setWorkflow(false);
grRP.autoSysFields(false);
grRP.updateMultiple();

Mark this as Helpful/Correct, if Applicable.

Regards,

Sourabh

[_ChrisHelming]

You should check out event-driven recursion. This is a perfect use case for it. https://snprotips.com/blog/2018/10/11/how-to-do-massive-slow-database-operations-without-slowing-dow...

[Gunjan Kiratkar]

Hi @Rafmine ,

Don't forget to write 

grRP.setWorkflow(false); //Will not fire BR/email notifications

grRP.autoSysFields(false);

while updating any kind of records.    

var grRP = new GlideRecord('resource_plan');
grRP.addEncodedQuery("end_date<=javascript:gs.dateGenerate('2021-12-31','start')^state!=8");
grRP.query();
while (grRP.next()) {
grRP.state = '8';
grRP.setWorkflow(false); //Will not fire BR/email notifications
grRP.autoSysFields(false);  // your name will not appeared in updated by column
grRP.update();
}

I will recommend you to use Scheduled job - 1st priority Or Fix Script - 2nd Priority to update the records.

Please mark my answer as helpful/correct if it resolves your query.

Regards,

Gunjan Kiratkar

Consultant - ServiceNow, Cloudaction

Rising Star 2022