The Now Platform® Washington DC release is live. Watch now!
on 12-03-2020 09:20 AM
< Previous Article | Next Article > | |
Performance Best Practices for Server-side Coding in ServiceNow | Caching data to improve performance |
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!
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.
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]
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 |
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?
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.
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.
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?
Thanks for bringing up this question
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.
Sincerely, your Global Technical Support Performance Team
Good points and I appreciate the detailed explanation! Thank you.
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
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?
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.
@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.
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://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"