Hello Everyone,
I came across a need where I had to run a scheduled job repeatedly till it updates all the records in batches in one of the task extension tables, the catalog task table. I decided to utilize system property to save the initial record created date time, and then wanted to process records created on each week.
And the reason for doing this instead of updating all records at once is: If I query all records (millions) at once system loads all the records into memory and it can cause performance issues in production. And another reason is when I was updating all the records, system was triggering text index events (rightfully so), and millions of text index events were piling up in short duration. In production if there are millions of text index events piled up, then they could take many hours to process. The global searches dependent on the processing of the text index events will be affected.
STEP 1: I need update a property with a date time value without flushing the system cache
Initially I tried to create a system property with “Ignore cache” checked. I ran into a problem where the scheduled job is processing the same week records again and again. Although the property was updated, it is not updated in all the application nodes. Each application node has its own copy of system properties since they are cached. If the cache does not refresh, then my system job will pick an old, cached value from system property rather a new updated value.
But if I uncheck the “Ignore cache” flag, then every time I update the property, system will clear the cache on the instance and if it happens during business hours in prod, user may report slowness in the instance.
There is a nice article on how to build cache to improve performance of queries, if you go this article and check the comments made by "ServiceNowPerformanceGTS" you will see nice useful information on how system properties work. Please refer below link. I personally believe that it has some great content.
Caching data to improve performance
I created a custom table which I can utilize instead of sys_properties table for scenarios like this. And, I can use this table as a cache, when we need to save something on server side without the need to clear cache on instance. This is simple but providing the update set for this as a zipped file here [You may use it in your OOB instance if you feel that it would be useful in many scenarios for your. If not if you came across a better idea please do suggest]
Table Name for storing custom properties or preferences: “u_property_preference_cache” []
Created a customer property with name “scTask.fieldCopyJob.batchStartDateTime” as shown below
Table and field are optional. If you want to create properties which applies to specific table or field in specific table, you may do that.
If you want to create property or preference for specific user, you may do that too if there are any use cases.
If you want to access these properties, you may do using the global object “u_gs” (Created using script include from my update set above).
You may use below code to fetch these properties with “u_gs” global object methods.
u_gs.getProperty(property_name, table_name /*optional as per your need*/, field_name /*optional as per your need*/)
u_gs.getUserProperty(property_name, user_sys_id, table_name /*optional as per your need*/, field_name /*optional as per your need*/)
u_gs.setProperty(property_name, table_name /*optional as per your need otherwise pass empty string as ""*/, field_name /*optional as per your need otherwise pass empty string as ""*/, property_value)
u_gs.setUserProperty(property_name, user_sys_id, table_name /*optional as per your need otherwise pass empty string as ""*/, field_name /*optional as per your need, otherwise pass empty string as ""*/, property_value);Please go through the u_gs script include to understand how these methods work.
Before going to STEP2, we must make sure to create the property as shown in screenshot 1 above and add it to the update set using button as needed.
STEP 2: Creating a scheduled job which updates all records in a table in batches without causing memory issues and search issues as mentioned above.
Example Summary: In this example, I am copying the field values from one field to another field in sc_task table.
Please find the steps below.
1) Go to "System Definition" application and "Scheduled Jobs" in left nav.
2) Choose "Automatically run a script of your choosing" in the interceptor
3) Set it to run periodically every 30 seconds, and run as field to System Administrator
4) Provide condition as mentioned below [This conditions validates and makes sure that scheduled job script will only run after the previous text index events are cleared out or are within limits - In my case I want the job to run only if the text index events waiting to be processed are less than or equal to 1000] :
(function () {
var events = new GlideAggregate("sysevent");
events.addEncodedQuery("name=text_index^stateSTARTSWITHqueue^ORstateSTARTSWITHready");
events.addAggregate("COUNT");
events.query();
if (events.next()) {
if (events.getAggregate("COUNT") <= 1000) {
return true;
} else {
return false;
}
}
return false;
})();5) Now go to the Script block and provide the script as below. [This is just an example template you need to modify it as per your requirement]
In my business case, I had to copy value of a field from existing field to new field.
(function () {
/* Executes till the system updates all the records in catalog task table
in batches (Week by week records based on created date).
Deactivates itself once the all the records till current time are updated */
gs.log("Field Fix Script SJB :: Start Time: " + gs.nowDateTime());
var startDateTime, endDateTime, recordsUpdatedInThisBatch = 0;
var gdt = new GlideDateTime();
gdt.setDisplayValue(u_gs.getProperty("scTask.fieldCopyJob.batchStartDateTime"));
startDateTime = gdt.toString();
if ((gs.dateDiff(gdt.getDisplayValue(), ((gs.nowGlideDateTime()).getDisplayValue()), true) >= 0)) {
var copyFld2Fld = new GlideRecord("sc_task");
gdt.addWeeksUTC(1); // Process records by each Week
endDateTime = gdt.toString(); // Week after startDateTime
copyFld2Fld.initialize();
copyFld2Fld.addQuery("<old_field>", "!=", "");
copyFld2Fld.orderBy("sys_created_on");
copyFld2Fld.setWorkflow(false);
copyFld2Fld.addQuery("sys_created_on", ">=", startDateTime);
copyFld2Fld.addQuery("sys_created_on", "<", endDateTime);
var queryStartTime = (gs.nowGlideDateTime()).getDisplayValue();
copyFld2Fld.query();
var queryEndTime = (gs.nowGlideDateTime()).getDisplayValue();
gs.log("Field Fix Script SJB :: Current range getting processed (" + (startDateTime + ", " + endDateTime) + "):: Query Time (" +
gs.dateDiff(queryStartTime, queryEndTime, true) + " Seconds) :: No of records in this week (" + copyFld2Fld.getRowCount() + ")");
while (copyFld2Fld.next()) {
try {
if (JSUtil.nil(copyFld2Fld.getValue("<new_field>"))) {
copyFld2Fld.setValue("<new_field>", copyFld2Fld.getValue("<old_field>"));
copyFld2Fld.setWorkflow(false); //Stop BRs
copyFld2Fld.autoSysFields(false); //Stop SysField updates
copyFld2Fld.update();
recordsUpdatedInThisBatch++;
}
} catch (err) {
gs.log("Field Fix Script SJB :: " + err);
}
}
u_gs.setProperty("scTask.fieldCopyJob.batchStartDateTime", gdt.getDisplayValue());
} else {
current.active = false; // Marks the current scheduled job as inactive once the copy of all records are done
current.update();
gs.log("Field Fix Script SJB :: Final End Time: " + gs.nowDateTime());
}
gs.log("Field Fix Script SJB :: Records updated in this batch: " + recordsUpdatedInThisBatch);
gs.log("Field Fix Script SJB :: End Time: " + gs.nowDateTime());
})();
7) Submit the job and activate it.
The job will process all the records eventually. But it will also make sure that it will not queue many text index events to drastically impact the text searches for recently updated records.
Using the log statements, you will be able to check the progress of the job and how closer or farther away from the completion time (or) you can even use custom property preference cache record to see the progress.
Based on your instance data size, you may need to tweak this or make additional changes as appropriate.
In my case I processed records week by week, but in your case, you may want to process month by month or day by day or year by year. It all depends on table you are dealing with, what is the best interval that suits you.
Test thoroughly and carefully before using this approach in production!
Hope this helps at least some of you!
Thanks and regards,
Subrahmanyam Satti
