Optimizing Scheduled Jobs for Unarchiving the Incident

[shirleyB]

Optimizing Scheduled Jobs for Unarchiving the Incident Table in ar_incident: We have developed a script to bulk restore records from the ar_incident table to the incident table. This script needs to handle approximately 8 million records. How to  improve the performance and speed up the restoration process, what strategies or techniques can be done?

This is the script updated in out scheduled job

// Initialize Counters and Log Storage
var incidentRestored = 0;
var childRecordsRestored = 0;
var logData = "Incident Number, Restored Child Record Sys ID\n";
// Query for Archived Change Requests created on or before 2025-08-31
var gr = new GlideRecord("ar_incident");
gr.addEncodedQuery('closed_at>javascript:gs.dateGenerate("2018-05-30","23:59:59")');
gr.setLimit(25000); // Set to 2000 records for testing
gr.query();
//gs.info("Starting logging of archived incident requests...");
while (gr.next()) {
// Restore the Main incident Request
var archiveLogGR = new GlideRecord("sys_archive_log");
archiveLogGR.addQuery("id", gr.sys_id);
archiveLogGR.addNotNullQuery("archive");
archiveLogGR.setLimit(1);
archiveLogGR.query();
if (archiveLogGR.next()) {
var archiveRestore = new GlideArchiveRestore();
archiveRestore.restore(archiveLogGR.sys_id);
archiveRestore.restoreRelated(archiveLogGR.sys_id);
incidentRestored++;
//gs.info("Change request number: " + gr.number);
// Log Related Records
var gr2 = new GlideRecord("sys_archive_related");
gr2.addQuery("archive_map", archiveLogGR.archive);
gr2.query();
while (gr2.next()) {
var gr3 = new GlideRecord("sys_archive_log");
gr3.addQuery("archive_run", archiveLogGR.archive_run);
gr3.query();
while (gr3.next()) {
if (gs.getXMLText(gr3.payload, "//" + gr2.element) == archiveLogGR.id) {
// Restore Child Record
archiveRestore.restore(gr3.sys_id);
childRecordsRestored++;
//gs.info(" Child record sys_id: " + gr3.sys_id);
logData += gr.number + "," + gr3.sys_id + "\n";
}
}
}
}
}
// Create and Save the CSV File in sys_attachment ONLY IF logData is NOT empty
if (logData != "Incident Number, Restored Child Record Sys ID\n") {
var attachment = new GlideSysAttachment();
var fileName = "restoration_log_" + new GlideDateTime().getInternalFormattedValue() + ".csv";
var attachmentId = attachment.write(null, fileName, "text/csv", logData);
// Get the sys_id of the attachment record
var attachmentGr = new GlideRecord("sys_attachment");
attachmentGr.get(attachmentId);
var attachmentSysId = attachmentGr.sys_id;
if (attachmentSysId) {
//gs.info("Log file saved (sys_id): " + attachmentSysId);
}
} else {
//gs.info("No archived incident or child records found before the specified date.");
}
// Final Log Message
//gs.info("Logging complete!");
//gs.info("Incident Processed: " + incidentRestored);
//gs.info("Child Records Restored: " + childRecordsRestored);