How to optimise the fix script on RITM table which is going to update the 8 lakh records ?

[raj99918]

Hi 

I have written below 2 fix scripts which is going to update the 4 fields on RITM table for 8 lakh records fix script is working fine I have tested it in lower instance which having 30k records but I checked the prod it have 8 lakh records so am afraid to run it on prod so I want your suggesting on my below 2 scripts:

Below script is to set the close and closed by fields:

var ritm = new GlideRecord("sc_req_item");

ritm.addEncodedQuery("stateIN3,4,7^active=false^closed_atISEMPTY^ORclosed_byISEMPTY");

ritm.query();

while (ritm.next()) {

    var updatedOn = new GlideDateTime(ritm.sys_updated_on);

    var user = ritm.sys_updated_by;

    var id = new GlideRecord('sys_user');

    id.addQuery('user_name', user);

    id.query();

    while (id.next()) {

        ritm.closed_by = id.sys_id; // Closed By

    }

   ritm.closed_at = updatedOn; // Closed time

    ritm.setWorkflow(false);

    ritm.autoSysFields(false);

    ritm.update();

}

gs.info("Number of records to updated for closed and closed by: "+ritm.getRowCount());

Below script is to set the Duration and Business Duration fields:

 var schedule = new GlideSchedule(gs.getProperty('x_og_schedule_id')); // Default calendar of the system (Workday 8:00 - 5:00)

var ritm = new GlideRecord("sc_req_item");

ritm.addEncodedQuery("stateIN3,4,7^active=false^calendar_durationISEMPTY^ORbusiness_durationISEMPTY");

ritm.query();

while (ritm.next()) {

    var createdOn = new GlideDateTime(ritm.sys_created_on.getValue());

     var closedat = new GlideDateTime(ritm.closed_at.getValue());

    var duration = gs.dateDiff(createdOn, closedat, false);

    ritm.calendar_duration = duration;  // Duration

var opened_at = ritm.opened_at.getGlideObject();

var closed_at = ritm.closed_at.getGlideObject();

var business = schedule.duration(opened_at, closed_at);

ritm.business_duration = business; // Business Duration

    ritm.setWorkflow(false);

    ritm.autoSysFields(false);

    ritm.update();

    gs.info("Total Records to update the Duration and Business Duration: "+ritm.getRowCount());

}

[OlaN]

Hi,

Some pointers.

In both your scripts you add an encoded query that selects records that has either one value empty or another value empty, but both values are set within your script.

Is this intentional? You will overwrite values if any exists in these records.

In the first script you can (and should) force the query to only return one row by adding below. Because that's all you want the one row that matches the query.

var id = new GlideRecord('sys_user');
id.addQuery('user_name', user);
id.setLimit(1);  // add this
id.query();

You can add a counter variable that increases within the loop, instead of having the query evaluated again at the bottom, where you use the .getRowCount().
In general, you should avoid getRowCount when dealing with large queries.

And finally, I personally would recommend using the getValue and setValue (GlideRecord) methods whenever possible, instead of dotwalking as you do in the script.

[Ankur Bawiskar]

@raj99918 

try this -> Script to Set the Close and Closed By Fields

var ritm = new GlideRecord("sc_req_item");
ritm.addEncodedQuery("stateIN3,4,7^active=false^closed_atISEMPTY^ORclosed_byISEMPTY");
ritm.query();
var count = 0;
while (ritm.next()) {
    var updatedOn = new GlideDateTime(ritm.sys_updated_on);
    var user = ritm.sys_updated_by;
    var id = new GlideRecord('sys_user');
    id.addQuery('user_name', user);
    id.query();
    while (id.next()) {
        ritm.closed_by = id.sys_id; // Closed By
    }
    ritm.closed_at = updatedOn; // Closed time
    ritm.setWorkflow(false);
    ritm.autoSysFields(false);
    ritm.update();
    count++;
    if (count % 1000 == 0) {
        gs.info("Processed " + count + " records");
    }
}
gs.info("Total records updated for closed and closed by: " + count);

Script to Set the Duration and Business Duration Fields

var schedule = new GlideSchedule(gs.getProperty('x_og_schedule_id')); // Default calendar of the system (Workday 8:00 - 5:00)
var ritm = new GlideRecord("sc_req_item");
ritm.addEncodedQuery("stateIN3,4,7^active=false^calendar_durationISEMPTY^ORbusiness_durationISEMPTY");
ritm.query();
var count = 0;
while (ritm.next()) {
    var createdOn = new GlideDateTime(ritm.sys_created_on.getValue());
    var closedat = new GlideDateTime(ritm.closed_at.getValue());
    var duration = gs.dateDiff(createdOn, closedat, false);
    ritm.calendar_duration = duration;  // Duration

    var opened_at = ritm.opened_at.getGlideObject();
    var closed_at = ritm.closed_at.getGlideObject();
    var business = schedule.duration(opened_at, closed_at);
    ritm.business_duration = business; // Business Duration
    ritm.setWorkflow(false);
    ritm.autoSysFields(false);
    ritm.update();
    count++;
    if (count % 1000 == 0) {
        gs.info("Processed " + count + " records");
    }
}
gs.info("Total records updated for Duration and Business Duration: " + count);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@raj99918 

Hope you are doing good.

Did my reply answer your question?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@raj99918 

Hope you are doing good.

Did my reply answer your question?

If my response helped please mark it correct and close the thread so that it benefits future readers.