Instance performance impacted by slow load times on forms with "Table Name" fields like item_option_new and sys_script. - Known Error

Instance performance impacted by slow load times on forms with "Table Name" fields like item_option_new and sys_script. Description *** IMPORTANT INFORMATION ABOUT THIS KNOWN ERROR *** This PRB has been fixed, but this behavior is opt-in at the instance and field level and requires action to be taken to enable the fix. The section marked ' Workaround ' contains the fix. These steps are required to resolve the issue, even if an instance is on a fixed version . ***

Users may encounter this problem when loading a form page with one or more "Table Name" fields present. Table fields are often added by ServiceNow to metadata record forms.

Root cause of the problem

The default behavior when loading a form with a table field is as follows:

Load a list all Tables [sys_db_object] defined on the instance. For each Table determine if the user has access by checking if the user can view one or more of its Columns [sys_dictionary]. Serialize the filtered Table list as HTML and return it in the form response. This process may be slow on instances with a large number of Tables [sys_db_object], Columns [sys_dictionary], Column Labels [sys_documentation], Roles [sys_user_role], ACLs [sys_security_acl], or Access Roles [sys_security_acl_role]. Development instances often have large counts of these metadata records.

Symptoms of the problem

Users may describe the problem in one of the following ways.

When I edit Variables [item_option_new] the page takes a long time to load. When I edit Business Rules [sys_script] in Studio the page freezes. When I develop Business Rules [sys_script] the instance freezes and I can't load any pages. Opening a new browser or incognito window and logging in again fixed the problem. When I open Variables [item_option_new] the long running transaction notifications pops up. (General) The development environment is very slow and I can't do my work. The following frequently edited metadata record types have table fields on the default form view. Users who report slowness when editing any of these record types may be facing this problem.

Variable [item_option_new] and other catalog item metadata Business Rule [sys_script] UX List [sys_ux_list] List Control [sys_ui_list_control] Record Producer [sc_cat_item_producer] Module [sys_app_module] Menu Item [sp_rectangle_menu_item] This is not an exhaustive list. To find all table fields open the sys_dictionary list on the instance and filter by Type is Table Name (/sys_dictionary_list.do?sysparm_query=internal_type=table_name).

Additional considerations when determining if user reported symptoms are caused by table fields:

This problem is usually encountered on subproduction (i.e. development or test) instances. This is partly because metadata records are usually created and edited on subproduction instances, and partly because subproduction instances often have more tables, columns, and roles defined which triggers the root cause. While it is less common, the problem may also be present on production instances. The problem may be intermittent. A recent partial or full cache flush can trigger the issue. On development instances partial cache flushes may be triggered by unrelated development changes; a full system cache flush (/cache.do) is not required to cause the problem. The problem may only be reproducible for some users. When investigating consider filtering the logs by the user reporting the issue. When reproducing consider impersonating the user to confirm the problem is present for their account. Confirming the problem

Use the instance logs to confirm that table fields are the cause of user reported slowness.

Open the Transaction Log (/syslog_transaction_list.do). Add the following columns to the list: URL, Created, Created by, Response time, CPU time, Session wait time Filter the Created field for the approximate date and time when the user experienced the issue. Filter the Created by field with the user ID of the user who experienced the issue If the user reported the metadata record type that triggered the issue (e.g. sys_script) filter the URL field to show entries containing that table name (e.g. `urlLIKEsys_script`). If not move on to the next step. Sort the CPU time column to show the largest values first. The CPU time column shows the time in seconds spent building the form HTML among other activities. Find form load transactions where the CPU time is high (e.g. 10s or more). If the form has a table field that may be the reason it is loading slowly. Compare the CPU time to the Response time for transactions of interest. If the CPU time accounts for a large portion of the Response time (e.g. 60%+) this is another sign the table field may be the issue. If the Session wait time accounts for a large portion of the Response time there is likely a different root cause for the performance problem. Steps to Reproduce

1. Login to an instance with a large number of metadata records (e.g. 8k+ tables, 50k+ columns, etc.)

2. Open a form with one or more table fields like item_option_new.do or sys_script.do

Expected: The page should load like any other page, in or around 5 seconds

Current: The page can take up to a minute to load due to the processing time required to render the table field

3. Perform a full cache flush (/cache.do)

4. Open a form with one or more table fields like item_option_new.do or sys_script.do

Expected: The page should not take significantly longer to load after a cache flush

Current: The page can take significant additional time to load as expensive one-time permission check operations are performed for each table

Workaround This PRB has been fixed in Utah Patch 8, Vancouver Patch 4, Washington DC, and subsequent releases. The fix introduces new behavior where the table fields only return 25 results on form load. When the user searches inside the table field additional results are loaded on demand from the backend.

This behavior is opt-in at the instance and field level. Customers can add the load_options_on_demand=true boolean dictionary attribute to fields of type "Table Name" to enable lazy loading. The attribute must be added to each field individually (see script below for automation options). If the attribute is not enabled the previous behavior will be used (i.e. all tables will be processed and sent down in the form HTML response).

The following client script APIs will no longer work on the "Table Name" field when the load_options_on_demand attribute is set to true. These client script APIs operate based on all tables being available on the initial page load instead of paginated results:

g_form.getOption g_form.addOption g_form.removeOption getSelectedOption g_form.getControl(‘table_name_field’).options If your form has a client script using one of the APIs above ensure load_options_on_demand is not set to true for the target fields.

Upgrade concerns

If you manually add this dictionary attribute to the Attributes field on a "Dictionary Entries [sys_dictionary]" record, a "Customer Updates [sys_update_xml]" record will be generated, which will prevent the dictionary entry from being overwritten on upgrade. If you add this dictionary attribute using the script below, "Customer Updates [sys_update_xml]" records will not be generated, and these changes will be overwritten on upgrade for OOTB dictionary entries. If the issue persists after upgrading, re-run the script below.

Manual instructions

If not already created make a new System Property [sys_properties] record of type string with the name `com.glide.searchable_table_choicelist`. Set the value to `v2`. Import the attached XML file named "getSelectedOptionPatch.xml" into the ServiceNow instance (PRB1831545) Open sys_dictionary and find the desired "Table Name" type field. Add the boolean dictionary attribute `load_options_on_demand=true`. Repeat for all desired fields. Scripted instructions

If not already created make a new System Property [sys_properties] record of type string with the name `com.glide.searchable_table_choicelist`. Set the value to `v2`. Import the attached XML file named "getSelectedOptionPatch.xml" into the ServiceNow instance (PRB1831545) Run the script below in Background Scripts (/sys.scripts.do) to automatically add the dictionary attribute to "Table Name" type fields. This script will check for any client scripts relying on APIs that no longer work when the dictionary attribute is set and skip those tables/fields. (function() { var sw = new GlideStopWatch(); var counter = 0, errors = 0, readOnly = 0, tables = {} // Identify tables that will break if load_options_on_demand is set to true var gr = new GlideRecord("sys_dictionary"); gr.addQuery("internal_type", "table_name"); gr.query(); while (gr.next()) { var tableName = gr.getValue("name") || ""; var fieldName = gr.getValue("element") || ""; // Search Client Scripts var searchTerms = [ "g_form.addOption\\([\\\"']" + fieldName, "g_form.getControl\\([\\\"']" + fieldName + "[\"'].options", "g_form.getOption\\([\\\"']" + fieldName, "g_form.removeOption\\([\\\"']" + fieldName, "getSelectedOption\\(" ]; var re = new RegExp("(" + searchTerms.join("|") + ")", 'g'); var gr2 = new GlideRecord("sys_script_client"); gr2.addQuery("table", tableName); gr2.query(); while (gr2.next()) { var script = gr2.getValue("script") || ""; if (re.test(script)) tables[tableName] = 1; } } tables = Object.keys(tables).sort(); // Update tables (skip tables that will break) var gr = new GlideRecord("sys_dictionary"); gr.addQuery("name", "NOT IN", tables.join(",")); gr.addQuery("attributes", "DOES NOT CONTAIN", "load_options_on_demand"); gr.addQuery("internal_type", "table_name"); gr.setWorkflow(false); gr.query(); while (gr.next()) { var attrs = gr.getValue("attributes") || ""; if (attrs) attrs += ","; attrs += "load_options_on_demand=true"; gr.setValue("attributes", attrs); if (gr.canWrite()) { if (gr.update()) { ++counter; } else { ++errors; gs.print("ERROR: Unable to update record: " + gr.getUniqueValue()); } } else { ++readOnly; } } gs.print("Tables skipped: " + tables.join(", ")); gs.print("Number of records found: " + gr.getRowCount()); gs.print("Number of records updated: " + counter); gs.print("Number of records not updated due to error: " + errors); gs.print("Number of read-only records skipped: " + readOnly); gs.print("Time to complete: " + sw); })();