Fix script taking very long to run, any suggestions to improve run time?

[snow_beginner]

Hi,

We have the BT app on our system and the recent update of it has brought 1200+ catalog items, most of which we don't need. So there is a fix script which is written to delete them but it is taking nearly 20 hours to run when it does run. 

I ran it yesterday in the afternoon and it cancelled with the error 'Progress Controller was left in running state on startup' after a few hours because it deleted half them, but why did this error come? Also prior to this error, the script ran for 20 hours and deleted everything, but is there a way to reduce the run time. Script below:

var logString = '';

var grCatItem = new GlideRecord('sc_cat_item');
grCatItem.addQuery('sc_catalogs', 'ffa751051ba5f11857f521f6b04bcbc6'); //BT
grCatItem.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
grCatItem.query();

while (grCatItem.next()) {

    var catName = grCatItem.getDisplayValue();

    var catSysId = grCatItem.getUniqueValue(); //get sys_id of catalog item

    deleteQuestionChoices(catSysId);

    deleteQuestion(catSysId);

    deleteCatalogCS(catSysId);

    deleteCatalogUIPA(catSysId);

    deleteCatalogUIP(catSysId);

    deleteVarSetsCatlgItemRelation(catSysId);
	
    grCatItem.deleteRecord();

}

deleteVariableSet();

deleteCategories();

deleteCatalog();

gs.log(logString);

function deleteQuestionChoices(catSysId) { //now passes sys_ID instead of catalog name
    var countQchoices = 0;
    var grQsChoices = new GlideRecord('question_choice');
    grQsChoices.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grQsChoices.addQuery('question.ref_item_option_new.cat_item.sys_id', catSysId);
    grQsChoices.query();
    while (grQsChoices.next()) {
        countQchoices++;
        grQsChoices.deleteRecord();
    }
    logString += '\nQuestion Choices: ' + countQchoices;
}

function deleteQuestion(catSysId) {
    var countQuestion = 0;
    var grQs = new GlideRecord('item_option_new');
    grQs.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grQs.addQuery('cat_item.sys_id', catSysId);
    grQs.query();

    while (grQs.next()) {
        countQuestion++;
        grQs.deleteRecord();
    }

    logString += '\nVariables: ' + countQuestion;

}

function deleteCatalogCS(catSysId) {
    var countCat = 0;
    var grCatCS = new GlideRecord('catalog_script_client');
    grCatCS.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grCatCS.addQuery('cat_item.sys_id', catSysId);
    grCatCS.query();

    while (grCatCS.next()) {
        countCat++;
        grCatCS.deleteRecord();
    }

    logString += '\nCatalog CS: ' + countCat;

}

function deleteCatalogUIPA(catSysId) {
    var countCatUIPA = 0;
    var grCatUIPA = new GlideRecord('catalog_ui_policy_action');
    grCatUIPA.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grCatUIPA.addQuery('catalog_item.sys_id', catSysId);
    grCatUIPA.query();

    while (grCatUIPA.next()) {
        countCatUIPA++;
        grCatUIPA.deleteRecord();
    }

    logString += '\nCatalog UIPA: ' + countCatUIPA;

}

function deleteCatalogUIP(catSysId) {
    var countCatUIP = 0;
    var grCatUIP = new GlideRecord('catalog_ui_policy');
    grCatUIP.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grCatUIP.addQuery('catalog_item.sys_id', catSysId);
    grCatUIP.query();

    while (grCatUIP.next()) {
        countCatUIP++;
        grCatUIP.deleteRecord();

    }

    logString += '\nCatalog UIP: ' + countCatUIP;

}

function deleteVarSetsCatlgItemRelation(catSysId) {
    var countgr = 0;
    var grIoSetItem = new GlideRecord('io_set_item');
    grIoSetItem.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grIoSetItem.addQuery('sc_cat_item.sys_id', catSysId);
    grIoSetItem.query();

    while (grIoSetItem.next()) {
        countgr++;
        grIoSetItem.deleteRecord();

    }

    logString += '\nCatalog IoSetItem: ' + countgr + '\n';

}

function deleteVariableSet() {

    var countVarSet = 0;
    var grIONS = new GlideRecord('item_option_new_set');
    grIONS.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grIONS.addEncodedQuery('titleSTARTSWITHDSA');
    grIONS.query();

    while (grIONS.next()) {
        countVarSet++;
       grIONS.deleteRecord();
    }

    logString += '\nVariable Set Count: ' + countVarSet;

}

function deleteCategories() {
    var countCategory = 0;
    var grCtgry = new GlideRecord('sc_category');
    grCtgry.addQuery('sc_catalog', 'ffa751051ba5f11857f521f6b04bcbc6');
    grCtgry.query();

    while (grCtgry.next()) {
        countCategory++;
        grCtgry.deleteRecord();
    }

    logString += '\nBT Categories Count: ' + countCategory;

}

function deleteCatalog() {
    var countcatalog = 0;
    var grCat = new GlideRecord('sc_catalog');
    grCat.addQuery('title', 'BT');
    grCat.query();

    while (grCat.next()) {
        countcatalog++;
        grCat.deleteRecord();

    }

    logString += '\nBT Catalog Count: ' + countcatalog + '\n';

}

initially the script did not have the count variables, but had getRowCount() but I was asked to remove that because its not best practice in our company and the healthscan catches it, but would using that help? So, I will paste some of the script with how it was initially

function deleteQuestionChoices(catName) {

    var grQsChoices = new GlideRecord('question_choice');
    grQsChoices.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grQsChoices.addQuery('question.ref_item_option_new.cat_item.name', catName);
    grQsChoices.query();
    logBeforeDel += '\nQuestion Choices: ' + grQsChoices.getRowCount();
    while (grQsChoices.next()) {
        grQsChoices.deleteRecord();
    }
    logAfterDel += '\nQuestion Choices: ' + grQsChoices.getRowCount();

 the above script is a snippet of the big one and how it was originally set up.

Also, we do need about 100 of the catalog items from the 1200 so is there a way to only delete the ones we don't need. Any help would be great. Thanks!

[Stefan Reichelt]

Idea: Instead of counting each single row, use GlideAggregate queries. Then you could try it with deleteMultiple(). Maybe that's faster?

[snow_beginner]

Hi @Stefan Reichelt I don't have much experience with using glideAggregate() but wouldn't doing that require rewriting the entire script? If possible just to help me get started with this, can you maybe do a snippet of the code with your suggestions

[Stefan Reichelt]

Sure, I can give you some more insights.

GlideAggregate is the preferrable solution when it comes to counting, or any other kind of aggregation. That's also why you shouldn't use getRowCount().

Let me copy an example from the documentation (linked above):

var count = new GlideAggregate('incident');
count.addAggregate('COUNT');
count.query();
var incidents = 0;
if (count.next()) {
   incidents = count.getAggregate('COUNT');
}
gs.info('Number of incidents: ' + incidents);

As you see, it works similarly to GlideRecord, just that you add an aggregate query, and then retrieve it selectively afterwards. So if you now write a little function based on the snippet above, you can even do that count before and after the deletion.

Once you got rid of the counting, you can go the next step and use deleteMultiple() - after checking against the restrictions mentioned in the documentation.

Does that help further, or do you need more details?

If that still doesn't work, it may be better to do the mass deletion in chunks. For example, use setLimit() to delete in smaller packages. Then you can run the deletion in a scheduled job which deletes the entries in a controlled manner.

I can't go through your script row by row at the moment, but if you're new to ServiceNow development, I can highly recommend the this page. It contains lots of helpful hints how to make your scripts more performant.

[Sandeep Rajput · Accepted solution]

@snow_beginner Here is an updated version. Please check if it improves the performance.

var logString = '';

var grCatItem = new GlideRecord('sc_cat_item');
grCatItem.addQuery('sc_catalogs', 'ffa751051ba5f11857f521f6b04bcbc6'); //BT
grCatItem.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
grCatItem.query();

while (grCatItem.next()) {

    var catName = grCatItem.getDisplayValue();

    var catSysId = grCatItem.getUniqueValue(); //get sys_id of catalog item

    deleteQuestionChoices(catSysId);

    deleteQuestion(catSysId);

    deleteCatalogCS(catSysId);

    deleteCatalogUIPA(catSysId);

    deleteCatalogUIP(catSysId);

    deleteVarSetsCatlgItemRelation(catSysId);
	
    grCatItem.deleteRecord();

}

deleteVariableSet();

deleteCategories();

deleteCatalog();

gs.log(logString);

function deleteQuestionChoices(catSysId) { //now passes sys_ID instead of catalog name
    var countQchoices = 0;
    var grQsChoices = new GlideRecord('question_choice');
    grQsChoices.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grQsChoices.addQuery('question.ref_item_option_new.cat_item.sys_id', catSysId);
    grQsChoices.query();
    countQchoices = grQsChoices.getRowCount();
    grQsChoices.deleteMultiple();
    logString += '\nQuestion Choices: ' + countQchoices;
}

function deleteQuestion(catSysId) {
    var countQuestion = 0;
    var grQs = new GlideRecord('item_option_new');
    grQs.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grQs.addQuery('cat_item.sys_id', catSysId);
    grQs.query();
   countQuestion = grQs.getRowCount();
    grQs.deleteMultiple();
    logString += '\nVariables: ' + countQuestion;

}

function deleteCatalogCS(catSysId) {
    var countCat = 0;
    var grCatCS = new GlideRecord('catalog_script_client');
    grCatCS.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grCatCS.addQuery('cat_item.sys_id', catSysId);
    grCatCS.query();
    countCat = grCatCS.getRowCount();
   grCatCS.deleteMultiple();
    logString += '\nCatalog CS: ' + countCat;

}

function deleteCatalogUIPA(catSysId) {
    var countCatUIPA = 0;
    var grCatUIPA = new GlideRecord('catalog_ui_policy_action');
    grCatUIPA.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grCatUIPA.addQuery('catalog_item.sys_id', catSysId);
    grCatUIPA.query();
    countCatUIPA = grCatUIPA.getRowCount();
    grCatUIPA.deleteMultiple();
    logString += '\nCatalog UIPA: ' + countCatUIPA;

}

function deleteCatalogUIP(catSysId) {
    var countCatUIP = 0;
    var grCatUIP = new GlideRecord('catalog_ui_policy');
    grCatUIP.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grCatUIP.addQuery('catalog_item.sys_id', catSysId);
    grCatUIP.query();
   countCatUIP = grCatUIP.getRowCount();
    grCatUIP.deleteMultiple();
    logString += '\nCatalog UIP: ' + countCatUIP;

}

function deleteVarSetsCatlgItemRelation(catSysId) {
    var countgr = 0;
    var grIoSetItem = new GlideRecord('io_set_item');
    grIoSetItem.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grIoSetItem.addQuery('sc_cat_item.sys_id', catSysId);
    grIoSetItem.query();
    countgr = grIoSetItem.getRowCount();
    grIoSetItem.deleteMultiple();
    logString += '\nCatalog IoSetItem: ' + countgr + '\n';

}

function deleteVariableSet() {

    var countVarSet = 0;
    var grIONS = new GlideRecord('item_option_new_set');
    grIONS.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grIONS.addEncodedQuery('titleSTARTSWITHDSA');
    grIONS.query();
   countVarSet = grIONS.getRowCount();
   grIONS.deleteMultiple();

    logString += '\nVariable Set Count: ' + countVarSet;

}

function deleteCategories() {
    var countCategory = 0;
    var grCtgry = new GlideRecord('sc_category');
    grCtgry.addQuery('sc_catalog', 'ffa751051ba5f11857f521f6b04bcbc6');
    grCtgry.query();
   countCategory = grCtgry.getRowCount();
   grCtgry.deleteMultiple();
    logString += '\nBT Categories Count: ' + countCategory;

}

function deleteCatalog() {
    var countcatalog = 0;
    var grCat = new GlideRecord('sc_catalog');
    grCat.addQuery('title', 'BT');
    grCat.query();
  countcatalog = grCat.getRowCount();
grCat.deleteMultiple();

    logString += '\nBT Catalog Count: ' + countcatalog + '\n';

}

Hope this helps.