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!

[snow_beginner]

Hi @Sandeep Rajput that helps a lot thanks and I will test it out but how does the glideAggregate() fit into this? I was struggling with using glideAggregate() because getRowCount is not supposed to be used.

[Sandeep Rajput]

@snow_beginner If you do not wish to use the getRowCount and still need the count of the affected record then you need to add separate GlideAggregate calls to each of your delete function. Here is an example.

function deleteQuestionChoices(catSysId) { //now passes sys_ID instead of catalog name
    var countQchoices = 0;
    var grQsChoicesAgg = new GlideAggregate('question_choice');
    grQsChoicesAgg.addQuery('sys_scope', '2add4be51b8110502c99a8e82d4bcb1d');
    grQsChoicesAgg.addQuery('question.ref_item_option_new.cat_item.sys_id', catSysId);
   grQsChoicesAgg.addAggregate('COUNT');
    grQsChoicesAgg.query();
    if (grQsChoicesAgg.next()) {
   countQchoices = grQsChoicesAgg.getAggregate('COUNT');
   }
    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;
}

Hope this helps.

[snow_beginner]

Hi @Sandeep Rajput  thanks so much for all your help with this. I tried this script as well as using the glideAggregate unfortunately it all takes very long time because its a very large number of records.