The Now Platform® Washington DC release is live. Watch now!

Help
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
GTSPerformance
Tera Guru
< Previous Article Next Article >
Performance Best Practices for Server-side Coding in ServiceNow Caching data to improve performance

Overview

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 Queries
    Many 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 Conditions
    Often 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 variations
    Avoid 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 manageability
    Keep 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 operations
    For 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


< Previous Article Next Article >
Performance Best Practices for Server-side Coding in ServiceNow Caching data to improve performance

 

Comments
Niclas
Mega Guru

Would the Before Query BR run on reportings?


E.g. if I have 10 Incidents, Before Query BR would return 8 Incidents when opening the list.

Now when I create an aggregate report like "Number of Incidents" would I see 8 or 10 Incidents? 

GTSPerformance
Tera Guru

Yes, Before Query BRs run during the execution of Reports. So in your example you would see 8 Incidents.

The exception to this statement would be reports whose backing data comes from pre-aggregated sources like Performance Analytics. In those cases, for example, a report on the Incident table would access data in a PA table and therefore not trigger Business Rules on the Incident table during report execution time. I'm not a PA expert and I am not familiar with how data access is managed in that feature, but strictly speaking, the operation that would be returning "incident" data from a PA table would not trigger a Before Query BR defined against the incident table.

Niclas1
Tera Contributor

Often in multi-national companies there are legal or compliance requirement to implement complex logics, who is allowed to read specific records.

 

When implemented with ACL, Users are constantly facing issue regarding the behavior of "xy records are hidden" that is displayed if ACL blocks read access. Unfortunately, the records are not sorted by visibility and split across pages, and Users don't like those fragmented lists.

When this issue was raised, in the past, the reply was often to use a Before Query BR as workaround.

This article is explaining very well the side effects of Before Query BR, that I also observed in praxis, so not actually a nice solution. 

Is there a hope that in the future, e.g. in the new San Diego UI Framework, the visibile records will be moved to the top to avoid the need of Before Query BR? Afterall, the related Community Idea is in the top 10 of the most voted ideas right now.

 

ITSM Dev
Mega Expert

As to Query BRs I remember being told that they were better than ACLs for performance reasons because Query BRs are executed only once per query and give the data filtering task to the background database. The explanation was that ACLs and other alternatives are instead executed for every single record returned from the database.

Why would ACLs be a better alternative for performance reasons?

 

GTSPerformance
Tera Guru

Thanks for bringing up this question @ITSM Dev,

I'm aware that this advice has been given out to folks over the years and I wouldn't be surprised if it was still being given out by ServiceNow trainers. The advice is not necessarily wrong, it is just too simplified to be the right answer all the time.

You mention two advantages of Query BRs, so I'll break my answer into two parts:

 

1. "Query BRs are executed only once per query"

I think this argument boils down to a comparison of how many executions of a Query BR must be run versus an ACL to accomplish the same goal. The argument kind of assumes that if there are fewer executions of a Query BR as opposed to the corresponding ACL, then the Query BR must mean better performance. For example, if you are doing an list load, then it is true a Query BR might have fewer executions than an ACL that is designed for the same purpose. For a list load there will be a single query to return all the results and then ACLs will be applied against each of the results that end up getting displayed in the UI. If your pagination setting is set to 50 then you would have 50 ACLs applied as opposed to 1 Business Rule. If the ACLs and BR have roughly the same net impact per execution then it would stand to reason that the Query BR would be a better choice, right? In reality, there are a number of ways that this argument breaks down.

There is the simple case that perhaps the ACLs and BR do not have the same net impact. Suppose the ACLs take 10 milliseconds each, for a grand total of 500 milliseconds, and suppose the Business Rule takes 1 second of execution time. Then it doesn't matter that there are fewer ACLs executed, they will be twice as fast as the Business Rule solution (500ms vs. 1,000m). This is not a far fetched proposition. In many cases, ACLs can be constructed in ways that make them incredibly simple and fast (see the list I put at the bottom).

A slightly more complex argument for why execution count does not make ACLs better lies in one of the fundamental differences between ACLs and Query BRs. ACLs apply their conditions against one record at a time. They ask things like, is this record owned by the current user? If we go back to our example before of 50 records displayed on the page, you can imagine how incredibly fast it would be to answer that question 50 times - especially since ACLs do not need to make a subsequent call to the database to answer the question, the app layer already has all the information it needs to answer the question. On the other hand, a Query BR will be adding that condition to the database query that executes against the entire table. The impact is magnified because more records must be checked to see if they are owned by the current user. If you have 5,000,000 records in the table then the database essentially has to run the question against 5,000,000 records.

One oversight regarding the statement "Query BRs execute only once per query" is that it only applies to certain cases - like when the operation is a list load. During a list load only a single query is executed, returning multiple records. However, there is also the possibility that the operation is something like the Activity Formatter where many queries are executed, one for each record returned. In this case, Query BRs lose their assumed advantage since there would be the same number of ACLs executed as there would Query BRs.

Another thing to consider is that Query BRs execute in many circumstances where ACLs do not execute. As mentioned in the above article, 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. This is probably the most important thing to think about. It can be somewhat mitigated by using the if (gs.isInteractive()) condition, which is understood to exclude things like scheduled jobs, but there are tons of weird exceptions. Like if a scheduled job does impersonation (like in a scheduled report) then it is still considered interactive. Also, gs.isInteractive() doesn't solve for cases where, for instance, an interactive transaction triggers some loop that is being executed as part of a Script Include, thus triggering thousands of GlideRecord calls against a table that has a Query BR. In that case an ACL wouldn't fire, but a Query BR would.

One more thing: table level ACLs will only be evaluated once per list load. Their results get cached and any subsequent execution has practically zero execution time. So if you are comparing Query BRs to table level ACLs then execution count advantage of Query BRs disappear.

 

2. "[Query BRs] give the data filtering task to the background database."

I think this part of the argument is predicated on the assumption that data filtering is always better handled by the database. I think it is probably one of those things that is mostly true when taken as a whole. However, it breaks down when you try to apply it to specific situations.

  1. In many cases, ACLs can completely avoid doing any expensive processing. If your ACL condition does not contain custom script, then it can be cached and executed incredibly fast. Going back and forth to the database is expensive and you can run into "death by 1,000 paper cuts" situations. ACLs have built in app tier caching while Query BRs do not. Our most frequent approach to improving Query BR performance is to add app tier caching.
  2. ACLs only run in contexts where security is needed, but Before Query Business Rules run everywhere. This is a very important point that I've already explained earlier. There are many things that can be done to mitigate the risk of this issue, but they usually involve complex customizations and deep understanding of custom business logic.
  3. ACLs usually do not add to the unpredictability of database queries. Predictable queries lead to consistent query patterns and consistent query patterns can be identified and optimized.
  4. Query BRs add to the complexity of database queries by adding conditions in various scenarios. As a general rule, the more complex the query, the less likely the database will be able to find an efficient way to execute it.
  5. ACLs are very easy to debug and see where they are coming from (see Session Debugging in our product docs). Query BRs, on the other hand, obfuscate the source of the problem. There is no easy way to tell that a given condition in a query has come from a certain Query BR. Thus, slow queries from Query BRs are likely to go un-diagnosed or misdiagnosed and therefore, unsolved or partially solved. This creates a long-term performance headache.
  6. ACLs have access to the "current" object for free - it is in memory already since the results have been returned from the database. Query BRs have access to a "current" object as well, but that object has not been populated with a result yet. The "current" object in Query BRs is basically a GlideRecord that hasn't had its query() method executed yet. In other words, because ACLs are run after results come back from the database, they can ask completely different questions than Query BRs. This fundamentally changes the way Before Query BRs must be designed. It often requires a Query BR to make queries to other tables to check for conditions and build lists of matching records. Or sometimes it necessitates the use of complex OR and JOIN operations that are difficult to optimize for the database.

 

Sincerely, your Global Technical Support Performance Team

ITSM Dev
Mega Expert

Good points and I appreciate the detailed explanation! Thank you.

GTSPerformance
Tera Guru

There is nothing in San Diego that will address the "xy records are hidden" message. It is possible that items may be on the road map for future releases. We will check.

The current reality is that using Before Query BRs for security requirements, while an admittedly challenging solution, is still the best option for many customers. The intent of this article is not to stop everyone from using them this way. The intent is to dispel any notion that Before Query BRs are a simple/easy fix.

For customers considering their options, ensure that you have a thorough discussion with the ServiceNow Solution team about other options. Your use case might be better addressed with another solution like Domain Separation or a Multi-Instance approach.

If you decide to go with Before Query BRs, understand that - as with any big decision - Before Query BRs will have their own type of technical debt. Your development team will need to understand how critical this code will be for your ongoing success. Making little changes here and there will have very far ranging effects. There will be a need strong governance and clear documentation regarding the design. Finally, you will need to ensure that anyone who touches the code has a deep understanding of the relevant best practice considerations.

Thanks!

Your ServiceNow Technical Support Performance team

Max Nowak
Giga Guru

Hey, thanks for the excellent article. I'm currently facing a similar situation as you described in the article about caching - the customer has a requirement that certain tasks are only visible to certain roles, and those roles are  added to contracts and service offerings (only one role per object though, not a list field). The visibility of a task is then defined by which service offering or contract is selected, and if the user has one of the roles that are set on those objects.

 

The query BR I wrote to achieve this is pretty ugly, and it causes noticable performance degradation in many cases. My question to you is, what's the best way to determine performance problems and establish some sort of baseline we could measure performance increases against? Currently, I'm looking at the transaction log, specifically the amount of BR executions and execution time. Is there anything else I should look out for?

ptyityt55
Giga Explorer

Does creating an adhoc roadmap with supply desk as undertaking making plans object isn't working? Ideally, if the source desk is task making plans item and if you have enabled showing object on bikerwhizz with milestones from personalization - Adhoc roadmap have to display all of the projects and its milestones.

GTSPerformance
Tera Guru

@Max Nowak You should also look at the "Slow Scripts" module and track the execution time of your Before Query Business Rule. You might also benefit from identifying any queries in the "Slow Queries" module that contain the sys_id of your Business Rule in the stack trace field.

Mwatkins
ServiceNow Employee
ServiceNow Employee

While not specifically performance issues, there are a couple ways that Before Query Business Rules can cause unexpected results if not carefully designed:

1. If you use an ^NQ in the encoded query that you add via the Business Rule, only the first half of the ^NQ conditions will include the original filter. See:

https://alps.devoteam.com/expert-view/servicenow-system-security-before-you-go-crazy-with-before-que...

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0564887

2. If you define your Before Query business rule on a specific child table in a hierarchy it will not be applied when someone queries the base table. For example, if you have a BQBR defined on the incident table it will not be applied if someone queries /task_list.do?sysparm_query=sys_class_name=incident.

 

Please Correct if this solves your issue and/or 👍 if Helpful

"Simplicity does not precede complexity, but follows it"

Version history
Last update:
‎12-03-2020 09:20 AM
Updated by: