Help
Join the Community
-
Documentation
Find detailed info about ServiceNow products, apps, features, and releases.
-
Impact
Drive a faster ROI and amplify your expertise with ServiceNow Impact.
-
Partner
Grow your business with promotions, news, and marketing tools for partners.
-
Store
Download certified apps and integrations that complement ServiceNow.
| < Previous Article | Next Article > | |
| Performance Hacks: Reference Fields (2 of 4) Autocomplete Wait Time |
Performance Hacks: Reference Fields (4 of 4) Contains vs. Starts With |
ServiceNow Reference Field
What really happens in the background when you type "A" in a reference field and ServiceNow spins and spins while you wait for it to bring back a list of suggestions? Who cares, just fix it Matthew‽ What's that weird punctuation I just used? It is an interrobang - I had to pull it out to describe just how frustrating a slow autocomplete can be. Anyway, in this article I'll discuss the way that the autocomplete columns that you specify on a reference field (via the ref_ac_columns attribute) impact the queries that are generated. More importantly, I'll discuss ways to improve those queries!
Without further ado, here is article #3 of 4:
The Problem
If you are searching more than one column, Reference Autocomplete uses OR operators to separate each condition. How many columns are in your autocomplete is controlled by the attribute named ref_ac_columns. Why is having multiple OR conditions a problem? For more info, see the following article: Database Performance: Improving Slow OR and JOIN Queries.
Suffice to say, if the predicates (i.e. the true/false conditions in your query) are joined with OR operators, then no single index can be used to match those conditions. Without an effective index, the database has to sort through lots of information either in memory or on disk and that is time consuming. An option for merging multiple indexes will be discussed later in this article.
| Big table + multiple ref_ac_columns = SLOW
So, are you saying I can't use multiple ref_ac_column values? No, I'm not saying that! Even the out-of-box product has reference fields with multiple ref_ac_columns in them. So, this is by no means an insurmountable problem. However, I have seen it go sideways in particular situations at scale.
A Real Customer Example
Customer X had a reference variable in Service Catalog pointing to the Company table (core_company).
Core_company had over 1.5 million records at the time.
The customer added a number of custom fields to the core_company table and included those fields in the ref_ac_column attribute on the reference field.
ref_ac_columns=name;banner_text;u_vendor_number;u_type
The queries resulting from auto-complete looked like this:
Time: 0:00:01.058 SELECT count(*) AS recordcount FROM core_company core_company0 WHERE ((core_company0.`name` LIKE 'a%') OR (core_company0.`banner_text` LIKE 'a%') OR (core_company0.`u_vendor_number` LIKE 'a%') OR (core_company0.`u_type` LIKE 'a%')) AND (core_company0.`u_active` = 1 AND core_company0.`u_vendor_number` IS NOT NULL AND (core_company0.`u_supplier` = 1 OR core_company0.`manufacturer` = 1))
Time: 0:00:05.197 SELECT core_company0.`sys_id` FROM core_company core_company0 WHERE ((core_company0.`name` LIKE 'a%') OR (core_company0.`banner_text` LIKE 'a%') OR (core_company0.`u_vendor_number` LIKE 'a%') OR (core_company0.`u_type` LIKE 'a%')) AND (core_company0.`u_active` = 1 AND core_company0.`u_vendor_number` IS NOT NULL AND (core_company0.`u_supplier` = 1 OR core_company0.`manufacturer` = 1)) ORDER BY core_company0.`name` limit 0,100
Note that the auto-complete conditions are all using the STARTSWITH search behavior. That is good because the database can use an index to serve a STARTSWITH query. See my next article about how to ensure your autocomplete is using STARTSWITH queries. However, those pesky OR operators make it impossible to use any single index to speed things up. This customer was having to wait over 6 seconds for autocomplete to work every time they entered a character into the reference field. What to do?
Some Solutions
Use only one column in ref_ac_columns
Sometimes you don't actually need multiple autocomplete complete columns. For example, there is no need to search both the sys_user.first_name and sys_user.name columns because sys_user.name implicitly starts with the same characters as sys_user.first_name. Sometimes stakeholders ask for multiple autocomplete columns even though it is not a dealbreaker requirement. If there is no way to make an autocomplete behave quickly given the original requirements, and some of the requirements are only nice-to-have's, then consider a tradeoff that favors performance over additional functionality.
Some tables automatically include a default field in their autocomplete configuration. Even if you only have one field specified in ref_ac_columns you might end up with a Database query that has two conditions in the WHERE clause, one for the default column and one for the column specified in ref_ac_columns.
Note that in order for the database to execute the autocomplete query efficiently, the column or columns you select for your autocomplete must have supporting database indexes where those columns are in the first position in the index. This will be explained more in the next section. If you find that an autocomplete depends on a field that does not have such an index, you can add them yourself through the UI. In ServiceNow, customers can add database indexes using the procedures described in Create a table index [official ServiceNow docs]. For a simple explanation of how indexes work, see Item #2 in Performance Best Practice for Efficient Queries - Top 10 Practices.
Index merge
An index merge [official ORACLE docs] is when there are multiple indexes that, when searched in combination, can quickly return the results of a WHERE clause whose predicates are separated by OR operators. In order to use an index merge you have to have an index for each field in the predicates that are separated by OR conditions. For example, the following query would need 3 indexes, one each, on email, first_name and last_name in order to leverage an index merge.
SELECT * FROM sys_user WHERE email LIKE 'a%' OR first_name LIKE 'a%' OR last_name LIKE 'a%' AND active = 1;
Note that each index can be a multi-column or composite index [official ORACLE docs], provided the autocomplete column is the first column in the index. For example, suppose you have first_name as an autocomplete column. Suppose further that you already have a three column index on sys_user(first_name, active, sys_created_on). There is no need to add a new index for just sys_user(first_name). The existing index can be leveraged by an index merge. While it is a composite index, as long as first_name is the first column in the index, it can be used in an index merge.
I've seen one case where all the indexes existed to allow an index merge but the database still wasn't choosing the index merge. In that case we were able to force the query pattern to use an index merge by adding an Index Hint/ReWrite. This is a functionality that requires help from ServiceNow support.
Add a restrictive reference qualifier and supporting index
Reference qualifiers [official ServiceNow docs] are sets of conditions to restrict the allowable values in a reference field. Reference qualifiers get translated into SQL conditions on every Reference Autocomplete query. If those conditions are sufficiently selective, then it won't matter how many OR conditions are also in the query. As long as the Reference qualifier conditions can efficiently leverage an index the query will be fast.
ServiceNow Support can create something called a Union Replacement
Call or open a Case for ServiceNow support and ask for help with your slow reference autocomplete operations. An index merge without a hint is the best option, but if all else fails, perhaps a UNION Replacement is the next option to try.
A Real Customer Example (...continued)
I'll bet you are wondering if we were able to help that customer with the slow reference field on core_company with 1.5 million records and multiple autocomplete columns. Good news, we were able to help them! We got those queries down from a combined 6-7 seconds to under 100 milliseconds each. We did it by adding indexes for each of their autocomplete columns so that the database could execute an index merge.
| < Previous Article | Next Article > | |
| Performance Hacks: Reference Fields (2 of 4) Autocomplete Wait Time |
Performance Hacks: Reference Fields (4 of 4) Contains vs. Starts With |
1While no single index can serve two conditions joined by an OR, there is the option of various index merge algorithms which can merge the results of two index search operations.
