BR causing the Performance issue for specific users

[Thej1]

Hi,

We have a before BR which is causing performance issue for the users of that group. The users are facing larger response time to perform the below operations.

Performance issue for the operations: 

1. To open list of Ritm's in backend

2. To open My Requests in portal

Here is the before BR:

Advanced condition - !gs.hasRole('admin') && gs.isInteractive(true)

Script:

(function executeRule(current, previous /*null when async*/ ) {

    // Add your code here

    var arr1 = [];

    var grDel = new GlideRecord("sys_user_delegate");

    grDel.addEncodedQuery("ends>javascript:gs.endOfToday()^delegateDYNAMIC90d1921e5f510100a9ad2572f2b477fe^approvals=true");

    grDel.query();

    while (grDel.next()) {

        arr1.push(grDel.getValue("user"));

    }

    var approverQuery = new GlideRecord('sysapproval_approver');

    approverQuery.addEncodedQuery('approver=' + gs.getUserID() + '^ORapproverIN' + arr1.toString());

    //approverQuery.addQuery('approver', gs.getUserID());

    approverQuery.query();

    // Get the sys_ids of RITM records where the current user is an approver

    var ritmSysIds = [];

    while (approverQuery.next()) {

        ritmSysIds.push(approverQuery.getValue('document_id'));

    }

    var taskQuery = new GlideRecord('sc_task');

    taskQuery.addEncodedQuery('request_item.active=true^assignment_groupDYNAMICd6435e965f510100a9ad2572f2b47744');

    //taskQuery.addQuery('request_item.state','in','1');

    taskQuery.query();

    while (taskQuery.next()) {

        ritmSysIds.push(taskQuery.getValue('request_item'));

    }

    var _officeManagementGroup = gs.getProperty('xl_office.management.group');

    if (gs.getUser().isMemberOf(_officeManagementGroup) && gs.hasRole('itil')) {

        current.addEncodedQuery('requested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORopened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORassignment_groupDYNAMICd6435e965f510100a9ad2572f2b47744^ORsys_idIN' + ritmSysIds+'^ORwatch_listDYNAMIC90d1921e5f510100a9ad2572f2b477fe');

     }

    else {

        current.addEncodedQuery('requested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORopened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORassignment_group!=' + _officeManagementGroup + '^ORassignment_groupISEMPTY^ORsys_idIN' + ritmSysIds+'^ORwatch_listDYNAMIC90d1921e5f510100a9ad2572f2b477fe');

    }

})(current, previous);

If condition query is taking larger response time.

I have tried by adding active true and created from last year in query, but no improve in the performance.

Can you please suggest any ideas to improve the performance by not changing the logic of code ?

Thanks

@Community Alums @Kieran Anson @Mark Manders @Amit Gujarathi @Arpan Baishya @Allen Andreas @poojasutar @Pradeep Sharma @Aditya Mallik 

[Community Alums]

Hi @Thej1 ,

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*
• • See Database Performance: Improving Slow OR and JOIN Queries
  • Make use of GlideRecord.addJoinQuery(table, field, field) where applicable. Counter-intuitively named, this method does a SQL sub-query not a JOIN. Sub-queries can help MySQL choose the optimum execution plan by essentially breaking a complex query into two simpler queries.
    
    
• 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.

From : Performance Best Practice for Before Query Business Rules

[Ankur Bawiskar]

@Thej1 

your script in query BR looks heavy

you are querying 3 tables overall; try to optimize that

[Thej1]

Hi Ankur,

Can you please let me know in How many we can optimize it ?

Can you propose any code changes ?

Thanks

[Kieran Anson]

Hi,

Can you elaborate on what this BR is trying to achieve? My assumption is as follows (running on RITM table)

1. If the user is a member of the office Management group, and has ITIL then:
   1. Only allow access where they are the requested for OR opened by OR assignment group OR watch list OR approver OR delegate OR a fulfiller of one of the sc_tasks for that RITM
2. If not a member of office management group then
   1. Only allow access where they are the requested for OR opened by OR assignment group of RITM is not office management group OR assignment group is empty OR approver OR delegate OR a fulfiller OR watch list.

In short, is the requirement just to hide RITMs assigned to office management from fulfiller users who aren't a member of the group, unless they have a sc_task assigned to them?