| < Previous Article | ||
| Performance Hacks: Reference Fields (3 of 4) The Autocomplete Query |
ServiceNow Reference Field
In this article I'll talk about some of the configuration options available to control if your reference autocomplete is going to use a "contains" or "starts with" search. As with all my articles, I'll discuss this from a performance standpoint.
Note that this article is only covering the behavior of the basic Reference field type. It does not apply to the behavior of other similar but different components like Reference Lookups, Context Search or some of the ServicePortal Reference Directives.
Thanks for those who have stayed with me to the end!
Without further ado, here is article #4 of 4:
The Problem
ServiceNow offers the ability to choose if Reference Autocomplete will use a "contains" or "starts with" search. If you choose "contains" then Autocomplete will look for any entry that has the autocomplete search term anywhere in its text. For example a search for the letter "a" would return any record with the letter "a" anywhere in the text. In SQL, this looks something like this:
SELECT ... FROM table_name WHERE ac_field LIKE '%term%'
The other option is to use a "starts with" search. In our previous example, a "starts with" search would only return results where "a" is the first letter of the result. In SQL, it would look like this:
SELECT ... FROM table_name WHERE ac_field LIKE 'term%'
This is a real big deal in terms of performance.
Contains style searches result in "scan" operations in the database
A "scan" in SQL is an operation that needs to read through every character of every entry in order to find results. The two common examples of scans are index scans that search through every entry in an index and table scans that do not leverage an index and must scan through entry entry in the raw table. Index scans and table scans can be very slow operations. Particularly on large tables or large fields.
On the other hand, a "starts with" style search is able to leverage an index to create a quicker result most of the time1. The reason this works is often explained using the analogy of how an index is like a phone book. To understand this, just imagine how much easier it is to lookup everyone whose last name starts with "waits" versus everyone whose name contains "waits" in a phone book.
Sometimes business requirements really need the autocomplete behavior to use a "contains" style search, but in many - perhaps most - cases it is a want not a need. There are ways in ServiceNow to provide some granular control of when "contains" is used versus when "starts with" is used. However, sometimes these methods are not well known. Another problem is that the less efficient method of "contains" is often used as the default behavior - as it is by default for all fields that point to the sys_user table. Let's look at some things that can be done to address these challenges.
Solution
glide.ui.ref_ac.startswith
If glide.ui.ref_ac.startswith is set to true then you can only default things to "starts with" (with one important exception on reference fields pointed to the sys_user table). This means you cannot use a user preference to override the default behavior to force a "contains" search. The only way to get a contains search in a reference field is for the end user to manually use a wildcard character at the beginning of their search term. For the desktop UI the wildcard is the star character (*). For the mobile UI it is the dot character (.).
Note that the actual description field of this property in the sys_properties table seems to indicate that wildcards cannot force a "contains" search, but that is NOT true. Beware, below is the misleading description that exists in the property out-of-the-box.
"Disable "contains" searches for reference autocompleters whether done with a wildcard (*) or the autocomplete.contains user preferences."
As you can see below, using a wildcard to trigger "contains" searches works just fine even when glide.ui.ref_ac.startswith is set to true.
As I alluded above, there is also a special case for the sys_user table. A User Preference record named "sys_user.autocomplete.contains" controls the default behavior of all reference fields pointing to the sys_user table. If this user preference is set to "true" (as it is out-of-the-box) it will override the behavior of the property, glide.ui.ref_ac.startswith. If you want to switch your sys_user reference fields to use "starts with" behavior, just change this user preference to "false". This might be a real big user experience improvement for your instance, especially if you have a couple million sys_user records. However, users would have to deal with the fact that their autocomplete behavior would have changed from "contains" to "starts with". Note below, the default behavior of any sys_user reference field is a "contains" search.
You might think that if you change the glide.ui.ref_ac_startswith property to false, you will see everything start to use "contains" searches by default. You would be wrong! If the glide.ui.ref_ac_startswith property is set to false then nothing changes - well almost nothing. Everything still defaults to using "starts with" searches unless there is a user preference specifying that the table being referenced use "contains". Out-of-the-box the only table that has a user preference set to use a "contains" search is still the sys_user table. However, if you make a copy of that user preference and change the name of the table in the preference name it will allow default "contains" search behavior on that table too. For example a global/system user preference named "core_company.autocomplete.contains" will result in the following behavior. Naturally, this would have a potentially negative impact on performance for very large tables.
| < Previous Article | ||
| Performance Hacks: Reference Fields (3 of 4) The Autocomplete Query |
1Sometimes a "starts with" query could actually be a little slower than a "contains" query.
For example consider the following. I have a table with half a million records. Each entry in the tutorial_title field starts with the prefix "Title" and ends with a number.
Search for '9'
mysql> SELECT COUNT(*) FROM tutorials_tbl WHERE tutorial_title LIKE '9%';
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM tutorials_tbl WHERE tutorial_title LIKE '%9%';
+----------+
| COUNT(*) |
+----------+
| 171950 |
+----------+
1 row in set (0.13 sec)
Search for 't'
mysql> SELECT COUNT(*) FROM tutorials_tbl WHERE tutorial_title LIKE 't%';
+----------+
| COUNT(*) |
+----------+
| 500000 |
+----------+
1 row in set (0.15 sec)
SELECT COUNT(*) FROM tutorials_tbl WHERE tutorial_title LIKE '%t%';
+----------+
| COUNT(*) |
+----------+
| 500000 |
+----------+
1 row in set (0.13 sec)
Search for 'Title5'
mysql> SELECT COUNT(*) FROM tutorials_tbl WHERE tutorial_title LIKE 'Title5%';
+----------+
| COUNT(*) |
+----------+
| 55550 |
+----------+
1 row in set (0.03 sec)
mysql> SELECT COUNT(*) FROM tutorials_tbl WHERE tutorial_title LIKE '%Title5%';
+----------+
| COUNT(*) |
+----------+
| 55550 |
+----------+
1 row in set (0.16 sec)
Notice that when searching for 't' the "starts with" operation is actually slightly slower than the "contains" operation (150ms vs. 130ms respectively). This is because every single entry in my table starts with the letter 't' and MySQL is able to run an index scan a bit faster than a range operation.
The thing to takeaway from this is that you should consider the breakdown of your data and how your users are using your autocomplete field. For example, suppose your field is matching against a field where all entries start with the same prefix. In that case, you won't get any improvement out of a "starts with" operation until the user has entered either something that is not the prefix. In such a case you might want to bump up the number of characters required to trigger autocomplete to the number of characters in the prefix + 1.
