Identifying and fixing slow queries in Service Portal Widgets - Support and Troubleshooting

Identifying and fixing slow queries in Service Portal Widgets Description This article describes how to identify and correct a slow query related to a specific Service Portal widget. This procedure is also appropriate for Self-Hosted customers.

NOTE : This article provides technical details only suitable for ServiceNow Support engineers and Self-Hosted customers, for example, query tuning and indexing are not relevant topics for hosted customers. The inclusion of this information is for reference only.

Release or Environment Service Portal

Resolution In this example, the Service Portal page /sc_home (Service Catalog home page) is slow to respond and taking nearly 15 seconds to load. Also, the TTFB (Time To First Byte) is high when measured in the Google Chrome or Microsoft Edge browser developer tools. These details are captured for reference.

Loading the page /sp?id=sc_home (a default ServicePortal page):

TTFB as shown in both the Microsoft Edge browser and Google Chrome. Note that the calls taking all the time are to sc_home.

Reproducing the problem As the maint or admin user, going to https://instance.service-now.com/sp?id=sc_home confirmed the same behavior as initially reported.

Loading of the page confirmed that there were two widgets on the page, with one a copy of the other with only the widget title change.

Looking at the Application node logs (or SQL Debug (Detailed), note the following two poor performing queries (highlighted in yellow).

The query times are highlighted in red, with the source of the poor performance shown in blue.

The LIKE condition performs a blind query that no index can improve. The result is a full table scan, as shown in the following explain plan.

The sc_cat_item.sc_catalog column is of type medium text and while an index might help in the case of a STARTSWITH (query bounded by % only on the left of the string), but using a CONTAINS query means no index can help and it guarantees a full table scan.

Note that the full sys_id being looked for is present (all 32 characters). What happens if you remove the LIKE condition and make it an equality?

The original query executed in ~5.4 seconds. Changing the query to use an IN operator allows the MySQL DB to optimize to use the existing indexing.

Refactoring the query provides the correct answer (result set) and speeds the calling Service Portal page significantly, but how can you be certain which widget is the issue and how to get at the actual GlideRecord query itself?

Identifying the slow Service Portal widgets Even though the reported problem has been reproduced, you can't be certain which widgets the sub-optimal SQL queries came from: the cloned widgets, the header widget, or some other Service Portal element on the sc_home page. To help identify which widgets were the cause, you need to use two bits of JavaScript code, entered from the browser console tab.

Widget Highlight script Entering the following code snippet in a browser console enables widget highlighting, widget names, and links to the widget in the editor, and the ability to print the scope of the widget to the console.

~~~snip~~~ $("div [widget='widget']").css("border", "1px solid red").css("padding-top", "20px").css("position", "relative").each(function(i){ var scope = $(this).scope(); var widget = scope.widget; var elem = $(" "+ widget.name +" "); var printScope = $(" Print scope ").on('click', function(){ console.info(scope); }); elem.append(printScope); $(this).append(elem); }); ~~~/snip~~~

Identification Procedure This example uses the Google Chrome browser.

While viewing the SP page to be inspected, open the browser Developer Tools. (Right-click Inspect or press Cmd + Option + J on MacOSX.) Click on the Console tab (#1 in the figure). Click the Clear Console button (#2 in the figure). Paste the Widget Highlight script.

The widgets should all display some new details, as shown in the screenshot (red lines around widgets, the widget titles, and the hyperlinked widget titles).

Notice the red lines around the edges of the widgets and the hyperlinked titles.

Obtaining Specific Widget Timing Widget Execution script (focused widget/element only):

The following code snippet allows for timed execution of a single (highlighted) widget on a page.

~~~snip~~~

$($0).scope().server.update()

~~~/snip~~~

This example continues to use Chrome Developer Tools to obtain specific widget timing. (Numbers refer to the callouts in the figure.)

Click on the Elements table (#3). Select Element Tools (#4). Select only the area of the widget that you want to execute and review the load timing details on (#5). Confirm the selected widget by clicking on the Element code (#6). Click on the Console tab (#1 in the previous screenshot). Click the Clear Console button (#2 in the previous screenshot). Paste in the one line of code for the single widget execution and press Enter.

Click on the Network tab and watch the single widget TTFB timing.

Note that the 5.55 seconds for the SC Recent Items widget to load is nearly equal to one of the slow queries shown at the beginning of this article. This sc_home portal page had two widgets on the page and two slow queries. Use these steps to confirm which other widgets are slow so that you can fix the same code (in this example, because the SQL queries had the same wildcard/blind query strings).

Note – You can enable SQL Debug (Detailed) and load the fake UI page /monkey.do to see the slow query being executed during the widget load in the above step.

Finding the GlideRecord query that generated the slow SQL Now that you have confirmed one of the slow widgets, chances are good this widget has the GlideRecord query that generates the problem SQL. Access the widget in the editor and see whether you can find the problem, then back up the widget (before making changes), and then change the query and test the results.

As shown in the previous screenshot, click the hyperlink text SC Recent Items. This will open the actual widget (not the widget instance) in the widget editor, as shown in the following figure. Note the following things in the screenshot: Selecting the Server Script checkbox by itself (#1 in the figure) enables you to visually focus on only the server side code to be reviewed. The line of code (#2 in the figure) trying to alter (but not in this current page!) Clicking on the context menu (hamburger icon) on the righthand side of the page and then selecting Open in Platform enables you to open the record in the core platform. Back up the widget and change the sub-optimal query. In the widget record (in the core platform), export the XML to preserve the current state before making any changes.

You know that line 10 in the screenshot results in a blind query in which no index can be used. (See the MySQL explain plan and timings above). You also know that the full sys_id is being used in the query, with a wildcard "%" on each side of the sys_id string. Using an IN range GlideRecord query will allow for an existing index on sys_id to be used instead of performing a full table scan.

Comment, copy and paste line 10 as follows: From ---> count.addQuery('cat_item.sc_catalogs', data,sc_catalog);

To ---> count.addQuery('cat_item.sc_catalogs', 'IN', data,sc_catalog);

Note – For additional GlideRecord API details, see API Reference - Server Side Scoped at the ServiceNow Developer site.

Save the record and with SQL Debug still enabled and Google Chrome Dev Tools turned on, reload the single widget (as you did with the one-line JavaScript code entered in the console). Confirm that the widget loads quicker and that the query itself is now much faster.

Also, with SQL Debug enabled, you can see that the GlideRecord query now being generated is an equality.

Apply the same fix to the second Service Portal Widget. Result Because the second widget on the sc_home portal page was a clone of the first with nothing other than the header of the widget having a different label, the exact fix is applicable and will reduce the portal page load time significantly. Use this procedure to isolate the slow query and prove out the fix after changing the GlideRecord query conditions.

Can an Active Query Rewrite be used instead? Yes, but use extra caution as it might not always behave the way you might expect it would. Sometimes the sys_id is interpreted as a string literal and only the actual string in the hint will get used (and not any other sys_id string - meaning it's not treated as a variable). This could lead to an unexpected result. Also, changing the widget code is straightforward and immediately obvious to the ServicePortal admin, whereas an Active Query Rewrite is only visible to users with the MAINT role (only ServiceNow TSE's and Self-Hosted customers) and the caller who reported the issue has no idea why the platform is behaving out of character. The following example and details show what the rewrite would look like. Also, consider that the rewrite does have some overhead and is not as quick as changing the source query in the widget.

The following figure shows the timing results with the rewrite.

Note : Be very careful if using an Active Query Rewrite

Additional Information Active Query Rewrites (not well documented) are very similar to Active Query Index Hints (which are well documented). For additional details, see the article Howto: Using "Active Query Index Hints" to improve slow query execution .