This guide is written by the ServiceNow Technical Support Performance team (All Articles). We are a global group of experts that help our customers with performance issues. If you have questions about the content of this article we will try to answer them here. However, if you have urgent questions or specific issues, please see the list of resources on our profile page: ServiceNowPerformanceGTS

This article assumes you are very familiar with scripting in ServiceNow already. It will discuss the performance implications and best practices regarding Before Query Business Rules (official ServiceNow product documentation link).

Business Rules are JavaScript that runs on Create/Retrieve/Update/Delete (CRUD) operations in ServiceNow. A Business Rule that runs before the retrieval of a record is sometimes called a Before Query Business Rule. Such a business rule will execute before every single user and background initiated request for data from a given table or its child tables (if it has children). Generally Before Query Business Rules are used to alter the query that is being issued using the current.addQuery() or current.addOrCondition() method or something like that.

Before query business rules run nearly everywhere!

• Lists (most obvious)
• Related lists
• Forms
• Formatters
• Activity Stream
• Dot walking
• Search results
• UI Actions
• GlideRecord queries

Because Before Query Business Rules run so frequently and they usually change the structure of the queries that are hitting the database (making them more complex), they can often be the source of severe performance degradation. Here's some general ways that we've seen this unfold.

Performance Risks when using Before Query Business Rules

• Unnecessarily Frequent Database QueriesMany Before Query Business Rules involve some query that has to be executed thousands of times per minute in situations where the answer is always the same, causing unnecessary load on the database. Consider that the solution you are designing might work fine when one user is accessing the data with only a few thousand records in the related tables, but that same solution will need to scale to millions of records and perhaps hundreds of simultaneous data access requests.
• Inefficient Queries Due to Custom ConditionsOften a Before Query Business Rule will use GlideRecord methods to add conditions to the "current" object. This, in turn, appends new conditional operations to the WHERE clause of the SQL statement that executes, making that query more complex and potentially less performant. In particular, 'contains' filters that search GlideList fields, like the watch_list field, are bad for performance.
• Unnecessary Code Executions from Other Scripts
  Before Query Business Rules get applied to queries originating from other scripts. This means that the added complexity and execution cost is being applied in places where it was never needed or intended. It also creates the potential for infinite recursion (A calls B calls C calls A calls...). This also makes it difficult to troubleshoot in the future since the developers of other scripts might wonder why they are getting unexpected results (NOTE: GlideRecord.setWorkflow(false) will allow the query to bypass Before Query Business Rules - use with caution since it will also bypass all other script engines and workflows, but not ACLs or security constraints).
• Technical Debt Due to Custom Code
  Finally, the added complexity of using a customized script causes performance issues in the sense that it makes future changes to code or usage more fraught with unknown risk. This is hard for ServiceNow to troubleshoot since we must try to understand a complex new customization. It makes it hard for customers to manage as well. The teams that end up managing the code are often not the teams who implemented it. Excellent in-line documentation is a must. And, ideally, a link to up-to-date comprehensive design documentation should be readily available as well.

Best Practices

• Favor solutions that will result in the fewest query pattern variationsAvoid creating dynamic code conditions that will result in hundreds of query permutations for a frequent data access operation. User A's query looks like one thing, user B's query looks like something else and so on.
• Create centralized Script Includes for manageabilityKeep the code that actually calls current.addQuery or current.addOrCondition in a central place. All Before Query Business Rules will call those Script Includes.
• Instead of making one monolithic query, use two or more queries and merge the results
  *NOTE: If you do this via code, it shifts the complexity to the application layer and may create more problems than it solves*
• Leverage some type of caching strategy
  If your Business Rule is making calculations based on data that does not change very often, you might be able to avoid running those calculations most of the time. For example, suppose your Before Query Business Rule puts a condition on task that restrict results to only those tasks that are in the user's region. Since a user's region does not frequently change, it is a good candidate for caching. Instead of adding logic that has to lookup a user's region over and over, consider if you can just cache each user's regional membership in memory. This could be accomplished using a method that only lasts the duration of the user's session or you could put a hard limit for "freshness" of cached data. This can done using an actual in-memory cache (e.g. gs.getSession().putClientData()) and/or using a "truth table" that stores the simple results of more complex calculations. See Caching data to improve performance for more details.
• Use code logic or data design to avoid inefficient operationsFor example, suppose you have two conditions joined with an OR statement like the following: department IN (1,2,3) OR region = 7. Perhaps there is some logical way to make the OR statement unnecessary based on information that is known prior to executing the query. For example, perhaps you know that departments 1,2,3 are inside (are a subset of) region 7 and therefore that part of the condition can be ignored. Or perhaps you can design your data in a way that you remove the need to look in both the department and region fields. Fixing through data design is probably the most overlooked option and the best strategic option.
• Put your data separation field directly on the table(s) that need to be separated
  Requiring your data separation logic to cause JOINs between multiple tables is a sure fire way to add complexity and reduce query efficiency. Instead, devise a way to have all the fields you need to accomplish data separation directly on the tables being separated. For example, if you want to separate the task table by region, don't require 3 JOINs by putting a condition on task.u_department.u_location.u_region! Have a u_region table directly on the task table.
• Do not allow separated records to belong to more than one data group
  If at all possible, find a solution that will enable you to represent group membership with a single value in a single field on any separated tables; e.g. a task should not be in both group A and group B. We have seen folks implement data separation by using a Glide List (glide_list) field on the table to be separated (task) with disastrous results. This may take some clever thinking to solve (ServiceNow's own Domain Separation solution solves this with the concept of Domain Paths (see Domain Separation - Advanced Concepts and Configurations), and Contains and Domain Visibility. Note, this does not mean a user cannot belong to multiple groups - it is referring to the data whose visibility is to be separated itself.

Before Query Business Rules and Data Separation Requirements

Roles, ACL's, Domain Separation and other out-of-box security features are the preferred and recommended method of applying data security and/or segregation to ServiceNow data. They are optimized for performance and cause less technical debt than a custom scripted Query Business Rule. Ideally, if you are attempting to achieve data security/segregation in a way that will result in some logical evaluation before every single call to a frequently accessed table, you should try very hard to use something other than a Before Query Business Rule. It should probably be a last resort when features that are actually designed to accomplish the same goal have been exhausted.

NOTE: If you feel that you must use a Before Query Business Rule (and I recognize that sometimes there just isn't another option) make sure you design it to be very efficient:

Official product documentation site: About Before Query business rules [for data segregation]

Additional Links

Performance Best Practice for Efficient Queries - Top 10 Practices

Performance Best Practices for Server-side Coding in ServiceNow

Labels:

• Multiple Versions
• Scripting and Coding