Performance Improvement - Remove Pagination Count - Support and Troubleshooting

Performance Improvement - Remove Pagination Count Background The results of most lists in ServiceNow are usually created using three queries. The queries are something like the following, where X is the user's pagination count preference:

A query that establishes the result count for the list SELECT count(*) as recordcount... WHERE [conditions] A query that brings back the sys_id's of the first X results SELECT table.sys_id... WHERE [conditions] LIMIT 0,X A query that fills out the field values based on the sys_id's gathered in the second query SELECT table.column1, table.column2, table.column3... WHERE table.sys_id IN (...X sys_id's...) Sometimes, the first query is exceptionally slow, while the second and third queries are very fast. This usually happens under the following conditions:

There is an index available to efficiently query the first X results of the list (therefore, queries #2 and #3 can be executed quickly) One of the following: The list has a million or more records in the result set. In this case, MySQL will have to do an index scan or a table scan, and this can take anywhere from 3 or 4 seconds to multiple minutes. When sorted, MySQL can quickly identify the first 20 records, but to find the total count, MySQL must read through a huge number of records. For example, suppose only 30 of 3 million records match a certain condition, and that condition cannot be matched using an index. To find the first 20 matching records without sorting them, MySQL has to read nearly 3 million records. However, suppose that all 30 records were created today, and the user puts a descending sort order for the Created field. In that case, MySQL will find the first 20 matching records relatively quickly, so queries #2 and #3 will be fast while #1 will be slow. In these cases, where query #1 is significantly slower than #2 and #3, the user is being blocked from seeing the data they want to see by a query that is arguably irrelevant to the business case. In other words, if I want to see a list of records matching some condition, I don't really care how many total records match my conditions. If I do care, it is more of a secondary concern. In such cases, it might be desirable to remove the pagination count functionality from the list.

Feature Summary Available starting in Orlando, the List Control configuration record has a new field named "Remove pagination count". No default behavior will change in Orlando, but administrators and developers can now leverage this configuration on a case-by-case basis to improve performance on desired lists. After activating the configuration, the List UI will no longer show the "of X" detail in the pagination area, and the platform will not execute the SELECT count(*) AS recordcount... query described above.

When to Remove Pagination Count Whoa there, horsey! Before removing the pagination count from all your lists on large tables, permit me to play devil's advocate. Consider some of the following negative consequences:

The configuration will likely only yield an improvement in cases where query #1 is significantly slower than query #2 and #3 If all 3 queries are slow, then it is very likely that a DB index or other method of performance improvement can be achieved. Even if query #1 is significantly slower, you should check the "Alternatives" section in the KB to see if a less invasive method will suffice to improve performance The "Remove pagination count" user experience removes any indication of how many records may be in the result set. If your result set includes more than your pagination count, the only way the UI tells you is that the right arrow ▶ button will not be grayed out. This may be disorienting for your user base. Some education of the user base would perhaps be prudent. Configuring a List Control will incur some minor technical debt. List Controls are tracked as Customer Updates and any future change to an out-of-box List Control would be blocked by the Upgrade Engine. List Controls are generally not changed from upgrade to upgrade, so this is not a high-risk change, but it is something to keep in mind. How To Remove Pagination Count Note: Not compatible with List V3 For Standard UI To Remove the pagination count from a single list/view, do the following:

From the desired List/View combination, right+click the header bar of the list and select "Configure > List Control" On the List Control, find the field "Remove pagination count", and skip to step #5 If the "Remove pagination count" field does not appear on the form, you will need to add it to the form, right+click the form header and select "Configure > Form Layout" Move the "Remove pagination count" field from the left slush bucket to the right slush bucket and click "Save" Set the "Remove pagination count" field to true and click "Update" This should automatically send you back to the list UI, where you will see the pagination widget no longer shows the "of X" text NOTE: Removing the pagination count can be especially helpful in the reference popup "ref" view - the view displayed when you click the magnifying glass icon of a reference field. Often, the reference popup view will not exist for a certain table. The easiest way to remove pagination from a reference popup is to click the magnifying glass icon of the desired reference field and then follow the steps above.

To Remove pagination count from multiple lists in one place:

Go to /omit_count_helper.do This will display the UI for the "Remove Pagination Count Helper for Large Tables" By default, only lists for tables with greater than 1 million total rows will show up; you can change this with the "Tables with Record Count Greater Than" field. To add list controls to all related lists and regular lists for a table, select the blue hyperlink for the desired table name from the list. If you want to add list controls for a table that is not listed (i.e. has fewer total rows than the "Tables with Record Count Greater Than" specifies), you can select any table name from the "Open Regular and Related Lists for Table" drop-down and then click the "Open" button. On the following screen, you will see a message indicating the following: Note: This will create List Controls where needed, using the default List Control values. If List Controls exist for a given table, a check box will appear for the relevant "View" and you check or uncheck "Omit Count" for that List Control. If you click "OK", the system will create UI List Control records wherever needed to remove the pagination count for every list for the selected table in the whole system. One thing to consider is that List Controls can be defined for a specific Related List or View. By removing the pagination count from only the most specific areas of slowness, you can reduce the potential negative impact.

For (Legacy) Agent Workspace To remove the pagination count for a specific known list, do the following:

Open "Workspace Experience > All Workspaces" Open the desired Workspace record In the "Workspace Lists" related list, click on the reference icon for the list you wish to change. In the "Workspace List Attributes" related list, click "New" Select "Omit Count" as the Attribute Click "Submit" To reverse the configuration, delete the List Attribute.

There is also a new UI created to manage changing List Attributes for multiple Lists at once for a given Workspace. To access this UI and make bulk changes:

Open "Workspace Experience > All Workspaces" Open the desired Workspace record In the "Related Links" area, click "Configure List Attributes" In the UI that comes up, click the checkboxes for the configuration change you want to make Click "OK" when ready to save changes For Table API REST calls By default, REST API calls via /api/now/table/ trigger a COUNT(*) query that is often not desired. To suppress the COUNT(*) query, add the sysparm_no_count parameter to the URL.

For Windowed GlideRecord calls The GlideRecord.query operation will automatically trigger a COUNT(*) query if the chooseWindow method is used. If you want to suppress the COUNT(*) operation in your windowed GlideRecord call, you can use the setNoCount() method. Here is an example:

The below script will trigger a COUNT(*) operation since chooseWindow has been used.

var prbGr = new GlideRecord("problem"); prbGr.chooseWindow(20,30); prbGr.query(); To use a windowed query and suppress the COUNT(*) operation, use GlideRecord.setNoCount() as follows:

var prbGr = new GlideRecord("problem"); prbGr.chooseWindow(20,30); prbGr.setNoCount(); prbGr.query();

Alternatives Loading Lists with "Filter Only" A little-known feature of ServiceNow is the ability to load a list with only the filter and breadcrumbs UI without loading any actual list results. This yields an instantaneous page load from which the end user can build their desired filter before executing a potentially slow transaction. To do this, the URL must include the "sysparm_filter_only=true" argument (a GET Request name/value pair). For example:

Will load a list UI with only the filter builder and breadcrumbs UI, but no results

/incident_list.do?sysparm_filter_only=true

Will load the first 20 records and the count of all incidents in the system

/incident_list.do

Overwriting Broad Filters Many out-of-box lists can become less performant as the data in a customer database increases. Some examples are things like "Configuration > Base items > All", "ECC > Queue", "System Logs > Transactions", "System Logs > System Log > All" or "System Definition > Deleted Records". In these cases you may want to remove these modules from the UI or replace them. You can make a copy of the modules and set active = false for the out-of-box version to avoid technical debt. Ideally, to improve performance, you would need to add some filter conditions so that the total count of matching records is much smaller and MySQL can quickly execute the count(*) operation. Strategies could include adding some type of "active" flag or a filter for recent entries based on an indexed date/time field (e.g. updated on Last 7 days)

Appendix In testing this feature, the following observations have been made:

If there are no records returned in ROWCOUNT, then we don't do the 2nd query... so... having rowcount on a list with no results does not incur a performance hit if the ROWCOUNT query would have been just as slow as the SYS_ID query. We did testing using real customer data sets. When testing just the slowest lists, turning off pagination yielded improvements in most circumstances. The benefits ranged from negligible to very significant, depending on the size of the result set. Slow lists were identified by the slow COUNT(*) queries related to them in the sys_query_pattern table. Examples

URL: /sc_req_item_list.do?sysparm_query=request.requested_for=abcabcabcabcabcabcabc123123123^ORu_for_user=abcabcabcabcabcabcabc123123123

Explanation: Filter was looking for SC Request Item records requested by a certain user or where the user was in the u_for_user field. Queries #2 and #3 were fast due to sort order on sys_created_on field but query #1 was taking about 12 seconds. Omitting the pagination count improved list rendering from 12 seconds down to about 1 second. However, this query could have probably been improved by adding the necessary indexes to allow SQL to do an index merge, and thus, removing the pagination count is probably not the right solution.

URL: /syslog_list.do?sysparm_query=sys_created_onONToday%40javascript%3Ags.daysAgoStart(0)%40javascript%3Ags.daysAgoEnd(0)

Explanation: This is the default module for "System Logs > System Log > All". It loads all logs created on "Today" - that's 27 million rows. When sorting on sys_created_on with a pagination count of 50, this list took over 10 seconds to render. After checking "Remove pagination count" the list renders in less than 1 second. This is probably the ideal situation for turning off the pagination count.