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.
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.
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?
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.
This example uses the Google Chrome browser.
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.
Widget Execution script (focused widget/element only):
The following code snippet allows for timed execution of a single (highlighted) widget on a page.
This example continues to use Chrome Developer Tools to obtain specific widget timing. (Numbers refer to the callouts in the figure.)
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.
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.
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.
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.
Also, with SQL Debug enabled, you can see that the GlideRecord query now being generated is an equality.
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.
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|
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.