Advance reference qualifier loads slow

[si21]

Hi experts, we have requirement for 'Employee name' field which refers to User table.

When a manager opens the form, they should see their 2 level reportee (i.e their direct reports and  reportee's reportees.) and when a HR opens the form, they should see all active users.

We are using below script include code and calling it on the 'Employee name' field which is working fine but it is  loading the data very slowly. 

Could please review and suggest any changes to code

-----------------------------------------------------------------------------

var checkManagerHR = Class.create();

checkManagerHR.prototype = {

    initialize: function() {},

    checkMgr: function() {

        var loginUser = gs.getUserID();

        //gs.info("mgr...HR" + loginUser);

        var reportees = [];

        var activeUsers = [];

        var gru = new GlideRecord('sys_user');

        gru.addActiveQuery();

        gru.addQuery('sys_id', loginUser);

        gru.query();

        if (gru.next()) {

            if (gru.u_hr_function != 'Human Resources') {

                var userGr = new GlideRecord('sys_user');

                userGr.addQuery('manager', gru.sys_id);

                userGr.addEncodedQuery('active=true^u_worker_typeLIKEEMPLOYEE');

                userGr.query();

                while (userGr.next()) {

                    reportees.push(userGr.sys_id.toString());

                    var userSysID = userGr.sys_id.toString().trim();

                    var managerGr = new GlideRecord('sys_user');

                    managerGr.addQuery('manager', userSysID);

                    managerGr.addEncodedQuery('active=true^u_worker_typeLIKEEMPLOYEE');

                    managerGr.query();

                    var rowCount = managerGr.getRowCount();

                    if (rowCount != '0') {

                        while (managerGr.next()) {

                            reportees.push(managerGr.sys_id.toString());

                        }

                    }

                }

                return 'sys_idIN' + reportees;

            } else if((gru.u_hr_function == 'Human Resources')) {

                var userHR = new GlideRecord('sys_user');

                userHR.addEncodedQuery('active=true^u_worker_typeLIKEEMPLOYEE');

                userHR.query();

                while (userHR.next()) {

                    activeUsers.push(userHR.sys_id.toString());

                }

                return 'sys_idIN' + activeUsers;

            }

        }

    },

    type: 'checkManagerHR'

};

-------------------------------

TIA

[Nick Parsons · Accepted solution]

I would suggest two changes, both involve removing the loops in your code (the main inefficiency)

For the non-HR employee:

- Don't try and get all the user sys_ids, instead, return an encoded query that finds these users for you. That means, return a query that's something like this, no more need for your loops/nested loops:

return "active=true^u_worker_typeLIKEEMPLOYEE^manager=" + gru.sys_id  + "^ORmanager.manager=" + gru.sys_id;

For the HR employees:

- You don't need to loop over all active HR employees and get their sys_ids. Instead, return a query that finds these for you. That means, return the following as your encoded query:

return "active=true^u_worker_typeLIKEEMPLOYEE";

There's now no need to loop through all your users, your reference field that uses the encoded query will do the looping in chunks (eg: 10 at a time) as you scroll down the users in the reference field list. 

See updated code:

checkMgr: function() {
  var loginUser = gs.getUserID();

  var gru = new GlideRecord('sys_user');
  gru.addActiveQuery();
  gru.addQuery('sys_id', loginUser);
  gru.query();

  if (gru.next()) {
    var activeEmpQry = "active=true^u_worker_typeLIKEEMPLOYEE";
    if (gru.u_hr_function != 'Human Resources')
      return activeEmpQry + "^manager=" + gru.sys_id  + "^ORmanager.manager=" + gru.sys_id;

    return activeEmpQry;
  }
},

[Mark Manders]

Using a GlideRecord query within a loop can cause performance issues. Your 'getRowCount' is also not the best, performance wise.

Can you check if this returns the same values, but faster:

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

    getReporteesOrHREmployees: function() {
        var currentUserID = gs.getUserID();
        var resultIDs = [];
        var grCurrentUser = new GlideRecord('sys_user');
        if (grCurrentUser.get(currentUserID)) {
            if (grCurrentUser.u_hr_function == 'Human Resources') {
                     resultIDs = this._getAllActiveHREmployees();
            } else {
                     resultIDs = this._getDirectReportees(currentUserID);
                var subReporteesIDs = [];
                for (var i = 0; i < resultIDs.length; i++) {
                    subReporteesIDs = subReporteesIDs.concat(this._getDirectReportees(resultIDs[i]));
                }
                resultIDs = resultIDs.concat(subReporteesIDs);
            }
        }

        return 'sys_idIN' + resultIDs.join(',');
    },

    _getDirectReportees: function(managerID) {
        var reporteeIDs = [];
        var grUser = new GlideRecord('sys_user');
        grUser.addQuery('manager', managerID);
        grUser.addQuery('active', true);
        grUser.addQuery('u_worker_type', 'LIKE', 'EMPLOYEE');
        grUser.query();
        while (grUser.next()) {
            reporteeIDs.push(grUser.getUniqueValue());
        }
        return reporteeIDs;
    },

    _getAllActiveHREmployees: function() {
        var hrEmployeeIDs = [];
        var grHR = new GlideRecord('sys_user');
        grHR.addQuery('active', true);
        grHR.addQuery('u_hr_function', 'Human Resources');
        grHR.addQuery('u_worker_type', 'LIKE', 'EMPLOYEE');
        grHR.query();
        while (grHR.next()) {
            hrEmployeeIDs.push(grHR.getUniqueValue());
        }
        return hrEmployeeIDs;
    },

    type: 'CheckManagerHR'
};

[Deepak Shaerma]

HI @si21 
1. Avoid Nested GlideRecord Queries
For the managers fetching their reportees and reportees’ reportees, you are initiating a new GlideRecord search within each iteration of an outer loop. This can be quite slow. Consider rethinking the logic to minimize database calls. Unfortunately, due to the hierarchical nature of what you’re trying to achieve (i.e., fetching reports and their reportees), getting it all in one efficient query could be challenging but optimizing or reducing any redundant checks could help.
2. Use getCachedQuery Answer or getKeys Method for Lightweight Checks
If the goal is to simply check if a user has reportees and you don’t need to process each reportee’s information, consider using getKeys() for a lighter query. However, this won’t directly reduce the number of calls if you’re still looping to fetch the reports of each reportee.

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

    checkMgr: function() {
        var loginUser = gs.getUserID();
        var reportees = [];

        // Check if the user is HR. If so, return all active employees.
        var gru = new GlideRecord('sys_user');
        gru.addQuery('sys_id', loginUser);
        gru.query();

        if (gru.next()) {
            if (gru.u_hr_function == 'Human Resources') {
                return this.getActiveEmployeesEncodedQuery();
            } else {
                return this.getReporteesEncodedQuery(loginUser);
            }
        }
    },

    getActiveEmployeesEncodedQuery: function() {
        var activeUsers = [];
        var userHR = new GlideRecord('sys_user');
        userHR.addEncodedQuery('active=true^u_worker_typeLIKEEMPLOYEE');
        userHR.query();

        while (userHR.next()) {
            activeUsers.push(userHR.sys_id.toString());
        }

        return 'sys_idIN' + activeUsers.join(',');
    },

    getReporteesEncodedQuery: function(managerSysId) {
        var reportees = [];
        var processedUsers = {}; // To avoid processing a user more than once

        // Retrieve direct reportees
        var directReportees = this.getDirectReportees(managerSysId);
        reportees = reportees.concat(directReportees);

        // Retrieve reportees of reportees
        directReportees.forEach(function(reporteeSysId) {
            if (!processedUsers[reporteeSysId]) {
                reportees = reportees.concat(this.getDirectReportees(reporteeSysId));
                processedUsers[reporteeSysId] = true;
            }
        }, this);

        return 'sys_idIN' + reportees.join(',');
    },

    getDirectReportees: function(managerSysId) {
        var reporteeSysIds = [];
        var userGr = new GlideRecord('sys_user');
        userGr.addQuery('manager', managerSysId);
        userGr.addEncodedQuery('active=true^u_worker_typeLIKEEMPLOYEE');
        userGr.query();

        while (userGr.next()) {
            reporteeSysIds.push(userGr.sys_id.toString());
        }

        return reporteeSysIds;
    },

    type: 'CheckManagerHR'
};

Note: Please Mark this Helpful and Accepted Solution. If this Helps you to understand. This will help both the community and me..
- Keep Learning ‌‌
Thanks & Regards 
Deepak Sharma

[Subhashis Ratna]

Hi @si21 

Your provided code is correct, but I'll optimize it further for better performance: 

Updated code : 

checkMgr: function() {
        var loginUser = gs.getUserID();
        var reportees = [];

        var gru = new GlideRecord('sys_user');
        //gru.addActiveQuery();  //I removed the addActiveQuery() call because it's redundant since you're already querying by sys_id, which is unique.
        gru.addQuery('sys_id', loginUser);
        gru.query();

        if (gru.next()) {
            if (gru.u_hr_function != 'Human Resources') { //if it's a reference field Here U can use sysID instead of DisplayValue
                this.findReportees(gru.sys_id, reportees);
            } else {
                reportees = this.findActiveUsers();
            }
        }
        
        return 'sys_idIN' + reportees.join(',');
    },

    findReportees: function(managerSysId, reportees) {
        var userGr = new GlideRecord('sys_user');
        userGr.addQuery('manager', managerSysId);
        userGr.addEncodedQuery('active=true^u_worker_typeLIKEEMPLOYEE');
        userGr.query();

        while (userGr.next()) {
            reportees.push(userGr.sys_id);
            this.findReportees(userGr.sys_id, reportees);
        }
    },

    findActiveUsers: function() {
        var activeUsers = [];

        var userHR = new GlideRecord('sys_user');
        userHR.addEncodedQuery('active=true^u_worker_typeLIKEEMPLOYEE');
        userHR.query();

        while (userHR.next()) {
            activeUsers.push(userHR.sys_id);
        }

        return activeUsers;
    },

SS - 1

If this solution resolves your query, kindly mark it as the accepted solution and give it a thumbs up.

Thanks,
Subhashis Ratna