Performance Hacks: Next Experience "Repeater" and "Pagination control" Components

Mwatkins · ‎04-02-2024

Introduction

I'm a performance engineer trying to learn UI Builder and Next Experience. Come along for the ride. Maybe you'll find it helpful. If you follow the step-by-step instructions, remember to test and save your work often!

Overview

Basically my strategy was to build something simple and then keep refining it.

In part one I populate a "Repeater" component with the contents of the Incident table.
Next I improve the performance by adding a custom limit to the number of Incidents
Next I add a "Pagination control" component and hook it up to the Repeater
Finally I make another performance improvement by stopping the COUNT(*) query from happening during initial page load and I add a button to trigger it on-demand.

For reference, my instance is set up with 1,000,000 Incident records. I did this so I can see scalability issues. Some customers have numbers much larger than this.

Create a new Page

Open "UI Builder"
Select your an Experience
Click the + icon and select "Create a new page"
Follow the instructions to build a new page from scratch

Create the Data Resource

Add a "Look Up Records" Data Resource and give it the id "incident_lookup"
1. Set table to "Incident"
2. Return number, sys_id, short_description, priority, state

Create the Repeater

Add a "Container"
1. Add a "Stylized text" component and set the text to "Incidents"
Add a "Container" and set it to vertical Flexbox
1. Add a "Repeater" component
  1. In the "Config" tab, set the Data Array to dynamic source and enter "@data.incident_lookup.results"
  2. Click the "Enable Styles" and set style to "Grid" with 3 columns x 2 rows
  3. Add a "Container" component
    1. Add "Stylized Text" with value "@item.value.number"
    2. Add "Label" labeled "Priority" with value "@item.value.priority.displayValue"
    3. Add "Label" labeled "State" with value "@item.value.state.displayValue"
    4. Add "Stylized Text" with value "@item.value.short_description.displayValue"

Performance Analysis

In my instance with 1,000,000+ incidents this takes about 32 seconds to complete. About 2-3 seconds are server response time to return the page. Then the component takes about 30 seconds to render on the client side before any of the cards in the repeater show up.

If you look at your transaction logs (/syslog_transaction_list.do) you will see one transaction named /$uxapp.do that takes about 2-3 seconds of "transaction_processing_time". This transaction is the basic workspace app shell builder and data resource load. Then there will be a gap of about 30 seconds before the next transaction which will be /api/now/v1/batch?api=api. The batch rest API is a way of bundling REST requests into a single transaction. This request and all the ones after it execute quite fast.

If you look in the browser debugger tools, you will see a similar story. There seems to be no long running transaction waiting on the server, just about 30 seconds where everything is pending.

In the left nav select "System Diagnostics > Session Debug > Debug SQL (detailed)". Open a new tab and put /duh.do in the address bar. Refresh your page and then refresh the /duh.do page again. This will allow you to see the SQL output from the Workspace page. Unfortunately Session Debug output is not compatible with Next Experience UI at the moment, so we have to open a page that is compatible to see the output. There are eleven queries against the incident table (actually the database name for the incident table is the task table). First there is a query that pulls the first 1,000 sys_id's and then there are 10 queries that pull all the other fields from incident, 100 records at a time. But the queries are fast. They finish in a few milliseconds.

So, next I used the Next Experience Developer Tools browser extension (an official ServiceNow product) to see what was happening during those 30 seconds. That gave me some very interesting information. I captured a Profile while loading the page and looked in the Events tab. It shows the following:

From this I derived that the execution time was being spent on client-side rendering, setting a property named nowUxfDbOutputIncidentRecords from 1,000 incident items. I made a guess that this was related to the "Look Up Records" Data Resource. By default, they have a record limit of 1,000! Apparently that is too much data for a Repeater to render in a timely fashion.

References

For information about this tool and how to download it

see https://developer.servicenow.com/dev.do#!/reference/next-experience/vancouver/developer-tools/using-...

For more scenario based training on the Next Experience Developer Tools see https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB1543474.

For more details the methods I'm using above to measure performance, see the public KB and video

see How to troubleshoot a slow transaction.

Add Limit to the Data Resource

The first thing we can do to speed this up is add a more restrictive limit to the Data Resource. No one really wants to see 1,000 detailed items at a time, it's not a good user experience.

Open the Data Resource
Change the "Max results" to something more reasonable, like 5.

Now the page will load almost instantly. When you look in the SQL Debug output now, you'll notice only the first 5 records are pulled back in the database query.

Add Pagination Control

To give the Repeater more capabilities we can add a Pagination control. In order to hook it up, we need to set up some page/client state variables to track the details needed for controlling the state of the Pagination component. We are also going to need to do one more query during page load to get the total count of records that match our record lookup.

Add the following "Client state" entries
1. "pageLimit" = 10
2. "pageOffset" = 0
3. "totalRecords" = 0
Add an "Aggregation Query" Data Resource
1. Set the data resource id to "incident_count"
2. Set "Table" to "Incident"
3. In the Events tab
  1. Add an event mapping
  2. Select "Update client state parameter"
    1. Put "totalRecords" as the Client State Parameter name
    2. Put "@data.incident_count.output.data.GlideAggregate_Query.0.count" as the New Value

Create a Client Script named "Pagination Handler"

Set the Script to the following:

function handler({api, event, helpers, imports}) {
    const newOffset = event.payload.value * api.state.pageLimit;
    api.setState('pageOffset', newOffset);
}

Set the following in your initial Record Lookup Data Resource (remember to set the field input type to "Bind data" - the little data source cylinder icon)
1. "Max results" = "@state.pageLimit"
2. "Pagination offset"= "@state.pageOffset"
Add "Pagination control" component right above the Repeater
1. In the Config tab, set "Total records" to "@state.totalRecords"
2. Set "Viewable items per page" to "@state.pageLimit"
3. NOTE: You do not need to map anything to the "Selected page index" field, but we will be using it's value in an event handler to update the pageOffset parameter, so that our Data Resource will move to the next chunk of X records.
4. In the Events tab
  1. Create a new Event Mapping
    1. Select "Pagination control selected page set" and select your Client Script to execute it
  2. Add another Event Mapping
    1. Select "Pagination control selected page size set"
    2. Select "Update client state parameter"
    3. Put pageLimit as the Client State Parameter name
    4. Put "@payload.value" as the New Value
Click "Save" and test

Performance Analysis

So, if I documented the steps correctly, and you followed along, then you should have a nice working, fully paginated Repeater component.

Unfortunately, in my case, with only 1 million matching records, the query to get the initial count of matching records takes over 7 seconds!!
09:34:04.53 Time: 0:00:07.884 for: [glide.11] [-837640149] SELECT ... FROM task task0 WHERE task0.`sys_class_name` = 'incident' AND (task0.`active` = 1 OR (task0.`opened_by` = '6a826bf03710200044e0bfc8bcbe5dec' OR ((task0.`priority` = 5 OR task0.`priority` IS NULL ))))

N.B. If you're trying to same experiment, you might be wondering why your query has different conditions than the one I'm showing above. My instance has a before/query Business Rule that adds some OR conditions to restrict only certain incidents to be seen. This is actually an inefficient way to restrict visibility. I did it as a proof of concept for troubleshooting a different performance issue. See Performance Best Practice for Before Query Business Rules for more information.

Also, note that this is the same problem that you would have with any similar list load with pagination. In order to fill in the "showing 1 to X of Y" you need to calculate Y. If a bunch of records match, then naturally, this COUNT(*) query will be somewhat slow if it is served from MySQL/MariaDB. This has always bothered me about ServiceNow. Why am I forced to wait for the calculation for the total number of matching records even when I'm limited to seeing only one page at a time? In most cases, I am not really interested in the total number of matching records, so why can't I opt out of that expensive query with a user preference or something? I think I'll solve that. Let's trigger the pagination count query only on demand.

Create a Button to Load Count On-Demand

In the Container with your Stylized Text that says "Incidents" add a Button Iconic component
1. In the Config tab
  1. Select Arrow Clockwise Fill as the icon
  2. Set Tooltip Text to "Load pagination count"
2. In the Events tab, select Add Event Mapping for when the button is clicked
  1. Set it to Refresh the Incident Count Aggregation Query component
Create a new Client State named "rangeLabel" with the initial value "Showing 1-5 of [click ↻ for total]"
Open the Pagination control component and set "Range label" to "@state.rangeLabel"

Create a new Client Script named "Update Range Label" with the following code

function handler({api, event, helpers, imports}) {
    const newTotalRecords = api.data.incident_count.output.data.GlideAggregate_Query[0].count;
    const adjustedOffset = api.state.pageOffset * api.state.pageLimit;
    const labelPrefix = "Showing " + 
        (adjustedOffset + 1) + "-" +
        (adjustedOffset + api.state.pageLimit) + " of ";
    const newRangeLabel = (newTotalRecords) ? labelPrefix + 
        newTotalRecords : labelPrefix + "[click ↻ to show count]";
    api.setState('totalRecords', newTotalRecords);
    api.setState('rangeLabel', newRangeLabel);
}

In the Incident Count Aggregation Query Data Resource
1. Set "When to evaluate this data resource" to "Only when invoked (explicit)"
2. In the Events tab
  1. Delete the old Update client state parameter
  2. Create a new Event Mapping to trigger the"Update Range Label" client script we created

Open the old"Pagination Handler" Client Script you created and change the code to the following so that the range label will get updated when we paginate:

function handler({api, event, helpers, imports}) {
    const newOffset = Number(event.payload.value);
    api.setState('pageOffset', newOffset);
    const recordStart = newOffset * api.state.pageLimit;
    const labelPrefix = "Showing " + 
        (recordStart + 1) + "-" +
        (recordStart + api.state.pageLimit) + " of ";
    const newRangeLabel = (api.state.totalRecords) ? labelPrefix + 
        api.state.totalRecords : labelPrefix + "[click ↻ to show count]";
    api.setState('rangeLabel', newRangeLabel);
}

Click "Save" and test

Casper6060 · ‎09-17-2024

Really nice document with valuable information to consider when working with larger data sets, thanks a lot for sharing!