question Re: Gauge Data Visualization UI Builder loads too slow in Next Experience forum

Gauge Data Visualization UI Builder loads too slow

Hari1 — Fri, 17 Jan 2025 12:48:43 GMT

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:

Re: Gauge Data Visualization UI Builder loads too slow

Brad Tilton — Fri, 17 Jan 2025 14:59:13 GMT

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/

Re: Gauge Data Visualization UI Builder loads too slow

tomasscerba — Fri, 17 Jan 2025 16:12:41 GMT

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=",

];

}

Re: Gauge Data Visualization UI Builder loads too slow

Hari1 — Fri, 17 Jan 2025 17:28:36 GMT

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

Re: Gauge Data Visualization UI Builder loads too slow

Hari1 — Fri, 17 Jan 2025 17:30:59 GMT

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.

Re: Gauge Data Visualization UI Builder loads too slow

tomasscerba — Fri, 17 Jan 2025 18:19:22 GMT

Well its hard to debug without actuall data.

make sure that your DR is executed only once, also how many records are in that table?

Re: Gauge Data Visualization UI Builder loads too slow

tomasscerba — Fri, 17 Jan 2025 19:03:42 GMT

Here's updated function that should work properly:

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();

let count;

while (cal.next()) {

count = cal.getAggregate("COUNT");

}

return 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=",

];

}

Re: Gauge Data Visualization UI Builder loads too slow

Hari1 — Sat, 18 Jan 2025 07:01:46 GMT

Hi @tomasscerba , I can see the values in the output object. But the execution time is now 11 seconds.

For your question "make sure that your DR is executed only once, also how many records are in that table?"
The data resource "When to evaluate this data resource" is set to "Immediately" as i need to data resource to run on page load and when the dropdown option is selected. For each query run from the script it returns a 80-50 thousand record count. The table total count is of 3,18,387 records.

Re: Gauge Data Visualization UI Builder loads too slow

tomasscerba — Sat, 18 Jan 2025 07:17:29 GMT

TBH that 11s is good number considering the computation that needs to happen. Also it was reduced from 30s to 11s. So thats a good start. The ammount of records are not small.

Also take into account sevicenow instance server response.

So my next question is, whether you need all the queries to be executed from array if dropdown is selected?

Re: Gauge Data Visualization UI Builder loads too slow

Hari1 — Sat, 18 Jan 2025 07:23:12 GMT

Yes, All the queries needs to be executed when the dropdown is selected. as i need to display the response object values in the data visualization and the labels.

When i try the same query in the UI Builder it takes longer 15~20 seconds in the background script it takes 11 seconds

Re: Gauge Data Visualization UI Builder loads too slow

tomasscerba — Sat, 18 Jan 2025 07:49:04 GMT

Does the customer need to have precise numbers at the time of the dropdown selected or each time dropdown is selected it can have rough estimate? What Im trying to acheive is to move that logic into scheduled job and result for both array queries will be stored into JSON field somewhere at your table. You can than simply query that one field without computation a display the data. Problem is how often will you need that data to be fresh, because you could slower the instance for all in general

Re: Gauge Data Visualization UI Builder loads too slow

Hari1 — Sat, 18 Jan 2025 08:45:14 GMT

So each time a dropdown option is selected the data resource runs and the query is updated with the dropdown option, For example from the code this line:

"operational_status!=6^owned_by.u_program=" + usedByOrgVal

Here usedByOrgVal will store the dropdown option.

Also when the dropdown option is "All", Which is the initial case when the page load the query will be without the usedByOrgVal value, For example from the code this line:

"operational_status!=6^owned_by.u_program"

How can we move the logic into a scheduled job? Can we try doing that approach?

Re: Gauge Data Visualization UI Builder loads too slow

tomasscerba — Sat, 18 Jan 2025 09:47:23 GMT

Before moving to that approach please read documentation how scheduled job works if you are not familiar with it. Maybe this functionality is not desired.

What i would try first is query all of the data immediatelly on page load and store them into state. Then i would use client script to determine what data will be loaded to that components from state.

You will have one big query at the beggining but then switching between values should be smooth

Re: Gauge Data Visualization UI Builder loads too slow

Hari1 — Sat, 18 Jan 2025 11:31:17 GMT

Please let me know if my understanding is correct.

Step1: Trigger the data resource on page ready and store the object output into the client state parameters using the client script.

Step2: Using the client state parameter value in the data visualization
Below is the snapshot:
Step 1:

Client Script:

Calling the client parameter value in the data visualization

It is still taking 18~20 minutes to load using this approach. Please let me know if this the approach that you were explaining.

I believe that the code in the data resource is taking long time due to the data pull through queries passed.

Re: Gauge Data Visualization UI Builder loads too slow

tomasscerba — Sat, 18 Jan 2025 12:30:44 GMT

you have 3 choices:

1. leave it as it is and each dropdown option will take 20 seconds to load

2. perform one big query to pull ALL IF AND ELSE queries together and cache them into client state and then pull the data from state according dropdown option selected. I would suggest to add loading for that so that user cannot interact with the page till it's not loaded

3. create scheduled job that runs once in some time period to perform both queries and store the result into JSON field. PLEASE NOTE: i would be very carefull with schedule jobs that are heavy for computation as they can slow down the entire instance for EVERYONE so make sure you trigger the scheduled job as few as possible and that you will test the performance. Once you have data available in your JSON field...all you need to do is to query that field to get the data...that should be super fast. The biggest downside of this approach is that you don't have fresh data untill next trigger of schedule job.

Re: Gauge Data Visualization UI Builder loads too slow

Hari1 — Sat, 18 Jan 2025 14:13:11 GMT

1. We cannot leave it as it is because the leadership teams do not want it to be so slow.

2. How do i perform a big query. I cannot use the GlideRecord in the client script. Any suggestions on that would be helpful

3. How do i trigger a schedule job from the UI Builder? I need more details on how can we achieve this step 3 functionality

Can you please help!

Re: Gauge Data Visualization UI Builder loads too slow

tomasscerba — Mon, 20 Jan 2025 08:01:32 GMT

1. create new field on table of type JSON

2. read how to create scheduled job (https://www.servicenow.com/docs/bundle/xanadu-platform-administration/page/administer/reference-pages/concept/c_ScheduledJobs.html)

3. store the response object from the script into that JSON field within scheduled job

4. on page load query that field, parse the response and use it in your compoentns

Re: Gauge Data Visualization UI Builder loads too slow

Hari1 — Tue, 21 Jan 2025 15:49:36 GMT

I have created a scheduled job and a custom table to store the JSON value. But i am not able to fine the data type JSON on the table field data type. Can you help me on how to create a field to store the JSON data?

NOTE: The JSON data is quite huge.

Some of the references i lookded at over the community but nothing helped.

https://www.servicenow.com/community/developer-forum/how-to-create-a-table-with-a-json-field-in-servicenow/m-p/2874808

Re: Gauge Data Visualization UI Builder loads too slow

tomasscerba — Tue, 21 Jan 2025 21:11:17 GMT

make it type string with 4000 max length. Don't forget to run both if and else queries and store the result into that field.

Re: Gauge Data Visualization UI Builder loads too slow

Hari1 — Thu, 23 Jan 2025 04:44:52 GMT

Thank you for the response, I am able to get this working, I was also looking at a approach were in i wanted to pass the object value to the system property instead of the custom table field. I am able to pass the value to the system property but when i access the system property in UI Builder i am able to see the output but with a backslash "\" in each object array value.
Below is the system property:

Below is the accessed system property in UI Builder:

Sorry i am asking for too much