Slow Query Performance Blues

[CJB]

Hi team. I have a query that seems to be working correctly, but I'm noticing performance issues. With the business rule, it takes about 10 seconds to open a CR, which I think will get even worse when there are multiple users on the system at once. I think the performance issue is due to all the change requests that are getting pulled in to the query.

At the bottom of the business rule, you'll see that I need a query will run for users. The business rule is supposed to return all records that do not have private data OR, if the record does have private data, the user will be able to view it if they opened/submitted the CR, are on the watch list, are a tech contact, or have an associated change task assigned to them or their group.

How should I optimize performance? Use ACL instead of a business rule? Can I use the business rule script I already built in an ACL? 

Any help would be appreciated. Thank you.

Script include

var E_AssignedTaskUtils = Class.create();
E_AssignedTaskUtils.prototype = {
    initialize: function() {},

    verify: function(
        userId
    ) {
        var grplist = new GlideRecord('sys_user_grmember');
        grplist.addQuery('user', userId);
        grplist.query();
        while (grplist.next()) {
            var group = grplist.group;
			var user = grplist.user;
            var gr = new GlideRecord('change_task');
            gr.addQuery('assigned_to', user)
//.addOrCondition('assignment_group', group);
            gr.query();

            return (gr.next());
        }
    },

    type: 'E_AssignedTaskUtils'
};

Business rule

(function executeRule(current, previous /*null when async*/ ) {
    var isAssigned = new E_AssignedTaskUtils().verify(gs.getUserID());
    //     gs.info('Approver ' + isApprover);
    if (gs.hasRole('private_data_viewer')) {
      
    } else if (!gs.hasRole('private_data_viewer') && (!isAssigned)) {
        current.addEncodedQuery('private_data=false^ORopened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORsubmitted_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORwatch_listDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORtechnical_contactDYNAMIC90d1921e5f510100a9ad2572f2b477fe');
    
    } else if (isAssigned) {
        array = [];
        var sys_tasks = new GlideRecord('change_task');
        sys_tasks.addQuery('assigned_to', gs.getUserID());
        sys_tasks.query();
        while (sys_tasks.next()) {
            var task = sys_tasks.change_request.getDisplayValue();
            changerequest = new GlideRecord('change_request');
            changerequest.addQuery('number', task);
            changerequest.query();

            while (changerequest.next()) {
                array.push(changerequest.number);
                //                 gs.info(array);
            }
        }
        current.addQuery('number', 'IN', array);
        current.addEncodedQuery('current.getEncodedQuery()^NQprivate_data=false^ORopened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORsubmitted_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORwatch_listDYNAMIC90d1921e5f510100a9ad2572f2b477fe');
    }
})(current, previous);

[Markus Kraus · Accepted solution]

I wanted to simply give you the optimized script, can you please confirm the following assumptions:

1.) The verify-function in the script include returns true if, and only if, the user with sys_id userId has a role assigned (which role doesn't matter) and is assigned to any change_task?

2.) The Business Rule is a query-BR and basically filters the tasks that a user should be allowed to access?

The problems is see here is:

1.) The BR is used to implement an ACL-like feature, this is not intended and an ACL should be used instead.

2.) Performance wise: Multiple while-loops where a single if statement would be sufficient, however thats not the problem as the while-loop is executed only exactly once. In this case setLimit should be added, additionally if you don't use the gliderecord itself, you can use hasNext instead of next.
a) In the Script Include, this code would be a lot faster:

var grplist = new GlideRecord('sys_user_grmember');
grplist.addQuery('user', userId);
grplist.setLimit(1);
grplist.query();
while (grplist.next()) {
    var group = grplist.group;
    var user = grplist.user;
    var gr = new GlideRecord('change_task');
    gr.addQuery('assigned_to', user)
    //.addOrCondition('assignment_group', group);
    gr.setLimit(1);
    gr.query();

    return gr.hasNext();
}

b) In the BR, you can use GlideAggregate instead of doing a nested GlideRecord query

(function executeRule(current, previous /*null when async*/ ) {
    var isAssigned = new E_AssignedTaskUtils().verify(gs.getUserID());
    //     gs.info('Approver ' + isApprover);
    if (gs.hasRole('private_data_viewer')) {
      
    } else if (!gs.hasRole('private_data_viewer') && (!isAssigned)) {
        current.addEncodedQuery('private_data=false^ORopened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORsubmitted_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORwatch_listDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORtechnical_contactDYNAMIC90d1921e5f510100a9ad2572f2b477fe');
    
    } else if (isAssigned) {
        array = [];
        var chgTaskGa = new GlideAggregate('change_task');
        chgTaskGa.addQuery('assigned_to', gs.getUserID());
        chgTaskGa.groupBy('change_request');
        chgTaskGa.query();
        while (chgTaskGa.next()) {
            array.push(ga.change_request.number.toString());
        }

        current.addQuery('number', 'IN', array.join(','));

        current.addEncodedQuery('current.getEncodedQuery()^NQprivate_data=false^ORopened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORsubmitted_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORwatch_listDYNAMIC90d1921e5f510100a9ad2572f2b477fe');
    }
})(current, previous);

[CJB]

Hi Markus,

Thank you very much for responding to my issue. As for your assumptions:

1.) The verify-function in the script include returns true if, and only if, the user with sys_id userId has a role assigned (which role doesn't matter) and is assigned to any change_task?

That's correct. Any user, regardless of role, can be assigned to a change task. The verify-function would return true if the user has any change tasks assigned to them.

2.) The Business Rule is a query-BR and basically filters the tasks that a user should be allowed to access?

That's correct. I am using the business rule to filter tasks based on whether the "Private Data" field is toggled.

If I were to use ACL instead of a business rule, could I still make use of the script include? I tried testing with a new ACL, but did not have any luck getting the ACL and script include to work with each other. My one (small) concern with using ACL instead is the prompt "Number of rows removed from this list by Security constraints." I wonder if the end user would be confused by this message?

Thank you.

[Markus Kraus]

Hm, given that i dont think ACL will improve that much to be honest. The Query BR is probably a more efficient way to do so.

In this case i think you could try the code i've posted as the first answer to this thread. Please test them, they should be fast enough performance wise.

[CJB]

That made a huge difference! In addition, I removed "current.getEncodedQuery()^NQ" and appended "^ORnumberIN' + array.toString()" at the end of the AddEncodedQuery instead, which also seemed to help. Thank you.