Optimize Gliderecord script to update more than 150000 records on CMDB table

[swapnil_singh]

Hi All,

I have a requirement where I need to update around 150000 records in Linux CMDB table and need to create a scheduled job which is required to be executed only once .

I have created the below script as per my requirement but it may cause an impact on the performance so can you help me for re optimizing or converting this GLiderecord into GlideQuery API.

 
var ser1 = new GlideRecord('cmdb_ci_linux_server');

ser1.addEncodedQuery('install_status=1^sys_updated_onONLast 30 minutes@javascript:gs.beginningOfLast30Minutes()@javascript:gs.endOfLast30Minutes()');
ser1.query();
while(ser1.next())
{
var rel = new GlideRecord('cmdb_rel_ci');
rel.addQuery('child', ser1.sys_id);
rel.addQuery('type.name', 'Virtualized by::Virtualizes');
rel.query();
while (rel.next())
{
// gs.log(rel.parent);
var ser = new GlideRecord('cmdb_ci_linux_server');
ser.addEncodedQuery('install_status=1');
ser.addQuery('sys_id', rel.parent);

ser.query();
while (ser.next())
{
// gs.log(ser.name);
ser.setValue('location', rel.parent.location);
ser.update();
}

}
}

Thanks

[Soeren Maucher]

Hello @swapnil_singh, 

the only way I see how you could optimize your script is that you use a database view connecting the tables cmdb_ci_linux_server and cmdb_rel_ci instead of doing the nested if loops. 

Since the script will only be executed once, I dont see an issue running it the way you have implemented it. 
I would however suggest using a "Fix Script" instead of a sheduled job if you only want to execute it only once. 

I hope this helped!

Greetings, 
Sören

[ersureshbe]

Hi, Can you use below link and use the same to resolve your end .

https://snprotips.com/blog/2018/10/11/how-to-do-massive-slow-database-operations-without-slowing-dow...

Regards,

Suresh.

[SanjivMeher]

You can set workflow as false, which should make it faster

var ser1 = new GlideRecord('cmdb_ci_linux_server');

ser1.addEncodedQuery('install_status=1^sys_updated_onONLast 30 minutes@javascript:gs.beginningOfLast30Minutes()@javascript:gs.endOfLast30Minutes()');
ser1.query();
while(ser1.next())
{
var rel = new GlideRecord('cmdb_rel_ci');
rel.addQuery('child', ser1.sys_id);
rel.addQuery('type.name', 'Virtualized by::Virtualizes');
rel.query();
while (rel.next())
{
// gs.log(rel.parent);
var ser = new GlideRecord('cmdb_ci_linux_server');
ser.addEncodedQuery('install_status=1');
ser.addQuery('sys_id', rel.parent);

ser.query();
while (ser.next())
{
// gs.log(ser.name);
ser.setValue('location', rel.parent.location);
ser.setWorkflow(false);
ser.update();
}

}
}