Performance best practices

About building efficient queries

ServiceNow allows users to build their own query filters in many areas of the product. Most notably, power users can build query filters on lists and reports. With this power and flexibility comes the potential for negative impact on performance. By knowing how to build efficient filters, power users will be able to get the information they need without negatively impacting performance of the system. The following is a list of things power users should consider when creating queries. This becomes extremely important when creating a query that will be run automatically many times per day, such as a gauge on your homepage that auto-refreshes.

These same tips also apply to administrator and developer users who design solutions by writing code or making configuration changes. Whether you are a power user, administrator or developer, these principles will go a long way to ensuring you and your team have a great experience with the ServiceNow platform. 

Limit the Rowcount User Preference 

Issue

Best practice for rowcount settings should be limited to 20 rows per page

Business Impact

When the rowcount setting is higher than 20 list rendering will be slow. If enough users have the setting above 20, list rendering can cause levels of database impact that can affect other transactions.

Affected

The user with high rowcount will experience slow list, homepage and form render times. If enough users execute inefficient operations at the same time this can result in a system wide performance degradation. 

Solution

There are three things that can be done to address the issue:

1. Individual users can change their "rowcount" user preference via the hamburger icon (three horizontal lines) on the list UI header.
2. Administrators can manually set the values of the rowcount preference through the module "User Administration > User Preferences" or the list below that already has the filter added for rowcounts: /sys_user_preference_list.do?sysparm_query=nameLIKErowcount%5Evalue!%3D20%5EORvalue%3DNULL%5Evalue!%3D50%5EORvalue%3DNULL%5Evalue!%3DNULL%5Evalue!%3D10%5EORvalue%3DNULL%5Evalue!%3D15%5EORvalue%3DNULL
3. Administrators can restrict the options that users are allowed to select by setting the "glide.ui.per_page" property

Use Database indexes with the most efficient operator for the job 

Databases indexes are very much like phone books. A Database index is a file that stores a sorted version of a table based on a certain field or fields. So, one index may be like a phone book that sorts the entries by the last name then first name. Another index may be like a phone book that sorts the entries by their phone numbers. It is important to make sure that you have the indexes you need to support your intended usage of the system. (see Create a table index) 

Whether you have a supporting index or not, you also need to consider the way your users will query the data in your tables. There are different types of operators that can be used to filter the data in your tables. It is critical that any commonly used filters (like a module or a report) are designed to use the most efficient operator for the job. "Equals" and "starts with" queries are more efficient than "contains" or "ends with". For example consider the relative difficultly of finding the following things in a phone book where the names are organized alphabetically by last name:

• Find people whose last name starts with "Bro"
• Find people whose last name equal "Brown"
• Find people whose last name ends with "own"
• Find people whose last name contains "row"

For the starts with and equals operators it is a fairly easy thing to do, right? But if you want to find someone whose name ends with "own" you'd have to read the whole phone book, reading the ends of each person's name. To find all the people with last names that contain "row",   you would have to read the whole phone book and you'd have to read the whole name of every person in the phone book - not just the end.

Add efficient Filter Conditions like active=true to improve slow queries 

Suppose you have a filter that is performing an expensive operation. For example, suppose you want to search the email table for a record whose subject contains a certain term. This is an expensive operation since it does a "STARTSWITH" operation on a huge table. To make this query more efficient, you should consider how you could add a second, more efficient condition to your query so that the total query will become more efficient. 

Original query string:

subjectSTARTSWITHChange Request

Improved query string using an additional condition:

sys_created_onONLast 7 days@javascript:gs.daysAgoStart(7)@javascript:gs.daysAgoEnd(0)^subjectSTARTSWITHChange Request 

Why does this work? The second query above limits the timeframe to be searched to only records created in the last 7 days - an efficient way of narrowing the result set. Databases build their final result sets by first creating intermediate result sets. By adding a condition that narrows the intermediate result set in an efficient way, the database can perform the less efficient operation — subjectSTARTSWITHChange Request - against a much smaller result set. This particular strategy is sometimes called time boxing. By executing an expensive operation within just a small box of time the total query execution becomes much faster. This usually requires that the table have an index on the field used in the time boxing strategy. 

Far and away, the #1 efficient filter condition that should be added whenever possible is "active = true". Most work that is done in ServiceNow is on a table that extends task. Suppose you have over 3,000,000 records in the task table and you want to query all the incidents that have the word "email" in their short description. Further suppose the following distribution of data:

Consider the following two queries:

• Original query string: short_descriptionLIKEemail^state!=6^state!=7
• Improved query string: short_descriptionLIKEemail^state!=6^state!=7^active=true

The condition on state field excludes the only case where active = 0. Therefore, by adding the active=1 condition we do not alter the end result of our query. However, by adding active=1 we get a very large benefit in terms of quickly reducing the intermediate result set. This often makes the difference between a query that takes over 30 seconds to complete versus one that executes in 1 or 2 seconds. Always add "active=true" whenever possible.

You can customize your lists and modules to force the active=true condition to stay in the breadcrumbs by using adding &sysparm_fixed_query=active=true to the URL. https://docs.servicenow.com/bundle/orlando-platform-administration/page/administer/list-administration/task/t_RestrictBreadcrmbsWFixedQueries.html

NOTE: One significant assumption being made here is that the number of active records in each table that extends task is relatively small. For active=true to improve your queries the number of active tasks must be small - under 10%. Out-of-the-box tasks will go to a closed state and the active flag will be set to false. However, sometimes customizations made to an instance result in tasks not going to active=false. If one table in the task hierarchy has many active tasks, this can affect query execution efficiency for all other types of task tables!! You should make sure that the percentage of tasks in your tables stays low - under 10%.

Avoid Conditions on Dot-Walked Fields

A "dot-walked" field in ServiceNow is a field that references a different table than the one currently selected, i.e. a Reference field. For example, a condition to check the name of the assignment group of a task would be a dot-walk from the task table to the sys_user_group table via the dot-walked relationship: task.assignment_group.name. When used in a condition, dot-walked fields create an implicit JOIN operation in the database between the related tables. Whenever possible, attempt to avoid conditions on dot-walked expressions. Relational databases are inherently bad at selecting good execution plans for JOIN queries between large data sets.

For example, the following filters return the same result sets but may mean the difference between a 10 second and a 10 millisecond query when dealing with millions of records.

Using a dot-walked field on the referenced table:

Using a direct reference field comparison:

Beware the Out-of-box "Go to" search option for lists

In the ServiceNow list header there is a search bar that allows you to narrow the list results by using a desired term against a particular field.

By default the "Go to" search will use the greater than or equal to comparison operator (>=). So, for example, in the screenshot above the system would do a search for any incident whose short description is greater than or equal to "SAP". This behavior is probably not what the customer desired or expected by default.

1. Set the property glide.ui.goto_use_starts_with = true (true as of Kingston on z-boots, see PRB1149592). For later versions, add the property to your sys_properties table and set it to true.
   
   
2. Use wildcard shortcuts with "Go to" for greater efficiency
   For example, by using SAP% instead of SAP, the search will return records whose short description starts with SAP.
   See the documentation page: Available list search wildcards
   
   
3. Avoid using the "goto_use_contains" property
   Changing the default behavior to do contains searches rather than >= searches is even less efficient.
   
   
4. [Available in Kingston] Specify the default "Go to" behavior on a per field basis
   This feature allows administrators to specify that certain fields should use a different default comparison operator. To activate:

   1. Upgrade to Kingston or later

   2. In the Dictionary record (sys_dictionary) for the field in question (e.g. task.short_description), add the attribute "goto_starts_with_search=true"

Break complex reports - especially ÜBER ORs - into multiple reports on a single Home page 

Homepages allow multi-threaded gauge processing. By default the multi-threading is set to 2. Therefore ServiceNow will process two reports on a homepage at the same time. You will be able to render three small reports faster than one big one. It is not always possible to break a complex query into multiple simple reports in a meaningful way without compromising the business requirements, but it should be considered. For example consider the following situation:

Big report takes 35 seconds:

(Closed today) OR (Opened today) OR (Active=true AND Assigned To=Service Desk) 

3 little reports:

1. Closed Today (1.3 seconds)
2. Opened Today (1.2 seconds)
3. Active=true AND Assigned To=Service Desk (2 seconds)

On a homepage with multi-threading of 3, the 3 little reports only take about 2 seconds total - the time it takes to render the slowest of the three reports.

This same principle also applies to you developers and administrators who are trying to write complex GlideRecord and GlideAggregate code. See the community article Database Performance: Ways to Improve Slow OR and JOIN Queries.

Design efficient Database Views

A Database View in ServiceNow is just a way of doing a JOIN query. Consider how you can include limiting conditions to create smaller intermediate result sets into the design of your Database View to avoid unnecessarily querying a huge dataset.

For example, suppose you build a Database View that joins Metric Definition with Metric Incident with Incident.   There is one like this that comes out of the box. It allows you to see different metrics in the same report. However, consider the case where you only want to see a certain Metric Definition. It may be much more efficient to add a condition to your Database View that restricts the results from Metric Incident to only the particular Metric Definition that you are interested in. Due to the shear size of the tables involved you may need to sacrifice the convenience of having every possible Metric Definition option available to be selected for the efficiency of just bringing back what you need.

Limit the number of columns that you see in List view

There is a list mechanic (cog wheel icon) feature on every list view in ServiceNow that will allow you to personalize the columns returned with every list query that you make. By reducing this list to only the specific columns that are of interest to you, the list rendering process can be greatly improved.

Avoid adding Journal fields to Lists 

The content of journal fields (e.g. "work notes" or "comments") are actually stored in a child table. When a journal field is displayed on a list it requires one extra query per row. With 50-100 rows per page this can result in diminished performance during list rendering. This can also be true with other large or dynamic fields such as the Workflow type field. When designing your UI, test to see if removing such fields from the list view has a large impact on performance.

Beware of your "Order By" field (sort order in lists)

One of the easiest ways to make a query run slowly is to "Order By" a field that does not support easy sorting. If you are performing a query that is running slowly, see if you can significantly speed up the query by removing the "Order By" field or selecting a different field upon which to perform the ordering.

If you cannot achieve your goal without performing a certain "Order By" operation, see if your administrator can add a Database index to the table that will support your query running more efficiently.

Databases almost always choose just one index per table when designing a query plan (there are rare exceptions like an index merge). One common situation is that the field that is being ordered on is forcing the database to take a sub-optimal query plan. For example, consider the following two time boxed queries:

SELECT ... FROM incident WHERE sys_created_on > "2017-06-20 01:00:00" AND sys_created_on < "2017-06-20 02:00:00" ORDER BY sys_created_on 

SELECT ... FROM incident WHERE sys_created_on > "2017-06-20 01:00:00" AND sys_created_on < "2017-06-20 02:00:00" ORDER BY opened_at

Let us suppose that both sys_created_on and opened_at have database indexes on them. Query A will operate lightning fast, because it can use the index on sys_created_on and only grab 1 hour of data. However, for query B the optimizer may choose to use the Database index on opened_at and now the database cannot assume that the results are ordered by the sys_created_on field, it must scan through every record in the opened_at index, looking for ones that fall within the 1-hour sys_created_on timeframe. So, use this principle, sort on the field that creates the smallest intermediate result set.

Load Related Lists on-demand

In the ServiceNow Fuji UI we added the ability to load Related Lists asynchronously or "on-demand". To change the default behavior for all users to be "On Demand" ServiceNow administrators can do it with the following user preference (note that the User field is intentionally blank to make this default for all users). 

Name: glide.ui.related_list_timing

System: true

User: <leave this field blank>

Value: deferred (or "ondemand")

With this in place, the user can still override the behaviour and change it to something else, but at least we start off with a clean slate. If any existing settings for glide.ui.related_list_timing exist then you can remove those to force them to honor the new default behavior. 

Pros:

• The top portion of all Forms will load very quickly.
• Users who are simply trying to update a record and do not need to see the Related Lists will have a much faster user experience (Related Lists are often the cause of 75% to 90% of form load latency).
• Users have the ability to override the preference for how their individual forms load if they do not like the behavior

Cons:

• Users who want to see the related lists may feel that the things they want to see are taking longer than before - especially if they are using the "ondemand" style list.
• Prior to Fuji Patch 5 this behavior had a user experience issue that caused the form to "jump down" to the related list section once the list completed rendering. Most users found this annoying. Fuji Patch 6 and later remove the "jump down" feature.

Minimize the volume and frequency of running Homepage loads 

One of the heaviest uses of ServiceNow is the automatically refreshing homepage. This is a very useful feature but can also lead to excessive system usage and performance degradation.

If a user's homepage is loading they will not be able to perform any other operation during that time. ServiceNow limits the number of concurrent transactions for each logged in user to 1 — this is known as session synch. To avoid running into session synch it is a good idea to reduce the frequency of your homepage auto-reload. 

If you have 200 users who want to see a certain report daily or weekly one idea is to have this done as a scheduled report that goes out to them via email instead of putting it on their Homepage. This is especially true for slow loading reports. A homepage will only load as fast as its slowest report. Avoid adding slow reports (ones that go more than 3 or 4 seconds) to homepages. 

If enough users have automatically loading homepages the combined impact can lead to performance degradation across the whole system during peak usage hours.

Select a very fast homepage as your default homepage. Your default homepage will be the last homepage you looked at. When you first log into the system your default homepage will be rendered automatically. If your colleagues all log in at the same time in the morning this can lead to a very heavy system load right when everyone logs in. A common customization is to create a homepage splash screen where users are redirected on login. See KB0712404 - Setting a Light Weight Home Page for Users for instructions about how to do this.