Gauge Data Visualization UI Builder loads too slow

[Hari1]

Hi,

I have added the Gauge Data Visualization component in UI builder which is taking long time to load (25~30 Seconds) when i preview it. 

Below is the customization that i have done in UI Builder

/**
  * @Param {params} params
  * @Param {api} params.api
  * @Param {TransformApiHelpers} params.helpers
  */
function evaluateProperty({api, helpers}) {
	var data = [{
        "data": [{
            "value": api.data.cmdb_ci_governance_server_1.output.Coverage[0].Percentage,
            "change": "0",
            "changePercent": "0"
        }],
        "metadata": {
            "eventData": {
                "indicatorSysid": ""
            },
            "dataSourceLabel": "",
            "filterQuery": "",
            "aggregate": {
                "fieldType": "decimal"
            },
            "format": {
                "unitFormat": "{0}",
                "frequency": "daily",
                "precision": 0
            }
        }
        
    }];
    return data;
}

Data Resource - Transform:

[
{
    "name": "ciType",
    "label": "CI Type",
    "description": "Type of the CI",
    "readOnly": "false",
    "fieldType": "string",
    "mandatory": true,
    "defaultValue": ""
},
{
    "name": "usedByOrg",
    "label": "Used By Org",
    "description": "Used By Org",
    "readOnly": "false",
    "fieldType": "string",
    "mandatory": true,
    "defaultValue": ""
}
]

Script:

function transform(input) {

	var start = new Date().getTime();

    var usedByOrgVal = input.usedByOrg;
    var arrFinCount = [];

    if (usedByOrgVal == "All") {
        arrFinCount = [filterList("operational_status!=6"), filterList("operational_status!=6^owned_by!=null"), filterList("operational_status!=6^u_in_coverage=Yes"), filterList("operational_status!=6^u_complete=Yes"), filterList("operational_status!=6^u_correct=Yes", "operational_status!=6^u_in_coverage=No"), filterList("operational_status!=6^u_complete=No"), filterList("operational_status!=6^u_correct=No"), filterList("nameISNOTEMPTY^operational_status!=6^u_resp_orgISNOTEMPTY^NQnameISNOTEMPTY^operational_status!=6^u_resp_orgISEMPTY^assetISEMPTY")];
    } else {
        arrFinCount = [filterList("operational_status!=6^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^owned_by!=null^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^u_in_coverage=Yes^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^u_complete=Yes^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^u_correct=Yes^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^u_in_coverage=No^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^u_complete=No^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^u_correct=No^owned_by.u_program=" + usedByOrgVal), filterList("nameISNOTEMPTY^operational_status!=6^u_resp_orgISNOTEMPTY^NQnameISNOTEMPTY^operational_status!=6^u_resp_orgISEMPTY^assetISEMPTY^owned_by.u_program=" + usedByOrgVal)];
    }

    function filterList(arrVal) {
        var cal = new GlideRecord("cmdb_ci_server");
        cal.addEncodedQuery(arrVal);
        cal.query();
        return cal.getRowCount();
    }

    var obj = {
        Ownership: [{
            Value: arrFinCount[1],
            Percentage: isNaN(((arrFinCount[1] / arrFinCount[0]) * 100).toFixed(2)) ? "0.00" : ((arrFinCount[1] / arrFinCount[0]) * 100).toFixed(2),
        }],
        Coverage: [{
            InCoverage: arrFinCount[2],
            OutOfCoverage: arrFinCount[5],
            Percentage: isNaN(((arrFinCount[2] / arrFinCount[0]) * 100).toFixed(2)) ? "0.00" : ((arrFinCount[2] / arrFinCount[0]) * 100).toFixed(2),
        }],
        Completeness: [{
            Complete: arrFinCount[3],
            NotComplete: arrFinCount[6],
            Percentage: isNaN(((arrFinCount[3] / arrFinCount[0]) * 100).toFixed(2)) ? "0.00" : ((arrFinCount[3] / arrFinCount[0]) * 100).toFixed(2),
        }],
        Correctness: [{
            Correct: arrFinCount[4],
            NotCorrect: arrFinCount[7],
            Percentage: isNaN(((arrFinCount[4] / arrFinCount[0]) * 100).toFixed(2)) ? "0.00" : ((arrFinCount[4] / arrFinCount[0]) * 100).toFixed(2),
        }],
        ActiveServerCount: [{
            Count: arrFinCount[8]
        }],
    };

    var end = new Date().getTime();
    var time = end - start;

    gs.info('CMDB: Execution time Server: ' + time);

    return obj;
}

[Brad Tilton]

My guess is the issue is your transform script. If you're looking to get record counts GlideRecord.getRowCount() is not a very performant way to do that. GlideAggregate is much faster if you're just counting records. Here are some resources that may help:

https://developer.servicenow.com/blog.do?p=/post/training-glideagg/ 

https://www.karteekn.com/gliderecord-glideaggregate-and-when-to-chose-one-over-the-other/ 

[Hari1]

Hi @Brad Tilton, I tried using GlideAggregate but the execution is increase by 2 seconds, I need the execution time to be below 5 seconds.

[IronPotato]

Hi @Hari1 ,

as Brad mentioned, try to convert your queries to GlideAggregate as they are much faster. I assume that you need to persist the order of the queries so I've changed it like this.

function transform(input) {

  var start = new Date().getTime();

  var usedByOrgVal = input.usedByOrg;

  var arrFinCount = [];

  // Arrays to preserve order

  var ifQueries = getIfQueries();

  var elseQueries = getElseQueries();

  if (usedByOrgVal == "All") {

    arrFinCount = filterList(ifQueries);

  } else {

    arrFinCount = filterList(elseQueries.map((query) => query + usedByOrgVal));

  }

  function filterList(queryArr) {

    return queryArr.map((query) => {

      var cal = new GlideAggregate("cmdb_ci_server");

      cal.addAggregate("COUNT");

      cal.addEncodedQuery(query);

      cal.query();

      return cal.getAggregate("COUNT");

    });

  }

  var obj = {

    Ownership: createMetric(arrFinCount[1], arrFinCount[0]),

    Coverage: createCoverageMetrics(

      arrFinCount[2],

      arrFinCount[5],

      arrFinCount[0]

    ),

    Completeness: createMetric(arrFinCount[3], arrFinCount[0]),

    Correctness: createMetric(arrFinCount[4], arrFinCount[0]),

    ActiveServerCount: [{ Count: arrFinCount[8] }],

  };

  var end = new Date().getTime();

  var time = end - start;

  gs.info("CMDB: Execution time Server: " + time);

  return obj;

  // Helper functions

  function createMetric(count, total) {

    return [

      {

        Value: count,

        Percentage: isNaN(((count / total) * 100).toFixed(2))

          ? "0.00"

          : ((count / total) * 100).toFixed(2),

      },

    ];

  }

  function createCoverageMetrics(inCoverage, outOfCoverage, total) {

    return [

      {

        InCoverage: inCoverage,

        OutOfCoverage: outOfCoverage,

        Percentage: isNaN(((inCoverage / total) * 100).toFixed(2))

          ? "0.00"

          : ((inCoverage / total) * 100).toFixed(2),

      },

    ];

  }

  function getIfQueries() {

    return [

      "operational_status!=6",

      "operational_status!=6^owned_by!=null",

      "operational_status!=6^u_in_coverage=Yes",

      "operational_status!=6^u_complete=Yes",

      "operational_status!=6^u_correct=Yes",

      "operational_status!=6^u_in_coverage=No",

      "operational_status!=6^u_complete=No",

      "operational_status!=6^u_correct=No",

      "nameISNOTEMPTY^operational_status!=6^u_resp_orgISNOTEMPTY^NQnameISNOTEMPTY^operational_status!=6^u_resp_orgISEMPTY^assetISEMPTY",

    ];

  }

  function getElseQueries() {

    return [

      "operational_status!=6^owned_by.u_program=",

      "operational_status!=6^owned_by!=null^owned_by.u_program=",

      "operational_status!=6^u_in_coverage=Yes^owned_by.u_program=",

      "operational_status!=6^u_complete=Yes^owned_by.u_program=",

      "operational_status!=6^u_correct=Yes^owned_by.u_program=",

      "operational_status!=6^u_in_coverage=No^owned_by.u_program=",

      "operational_status!=6^u_complete=No^owned_by.u_program=",

      "operational_status!=6^u_correct=No^owned_by.u_program=",

      "nameISNOTEMPTY^operational_status!=6^u_resp_orgISNOTEMPTY^NQnameISNOTEMPTY^operational_status!=6^u_resp_orgISEMPTY^assetISEMPTY^owned_by.u_program=",

    ];

  }

}

[Hari1]

Hi @IronPotato, I see the execution time is 10 seconds and the output object value is 0. Please find the below snapshot.