advance reference qualifier returning slow results

[archie5]

My requirement is on incident caller field, only those users should show that are active, employee number is not null and employee number is numeric. I am using this script include and calling it in my ref qual. But when I type in few letters in caller field, it takes forever to bring the results. Example, if I type Aar, it takes about 10 seconds to load Aaron.

Script include - 

getActiveNumericEmployeeUsers: function() {
var usersList = [];
var gr = new GlideRecord('sys_user');
// Properly filtering active users and ensuring employee_number is not null
gr.addQuery('active', true);
gr.addNotNullQuery('employee_number');
gr.query();

var regexp = /^[0-9]+$/; // Regex to check if employee_number is fully numeric
while (gr.next()) {
var employeeNumber = gr.getValue('employee_number');
if (employeeNumber && regexp.test(employeeNumber)) {
usersList.push(gr.getUniqueValue());
}
}

return usersList; // Returns an array of sys_ids
}

[Medi C]

@archie5 
Could you please check if the sys_user table is indexed with the fields used in your query?
And try the following:

getActiveNumericEmployeeUsers: function() {
    var usersList = [];
    var gr = new GlideRecord('sys_user');
    
    gr.addEncodedQuery('active=true^employee_numberISNOTEMPTY');
    gr.query();

    while (gr.next()) {
        var employeeNumber = gr.getDisplayValue('employee_number');
        
        // Check if employeeNumber is fully numeric
        if (/^\d+$/.test(employeeNumber)) {
            usersList.push(gr.getUniqueValue());
        }
    }

    return "sys_idIN" + usersList;
}

 
Then on your reference qualifier, you can simply call the function:

javascript:new YOUR_SCRIPT_INCLUDE().getActiveNumericEmployeeUsers()

In case it is still slow, could you please tell how many records are there in the sys_user table?

[archie5]

Thanks Medi C. 

I used your script and its still taking 5+ seconds to load. We are looking into 1-2 secs. Employee number and active both are indexed fields. Total user records - 33000, active and not nul - 11000 and numeric emp number - 8300.

[Medi C]

Let's try avoiding the regular expression by using the following:

getActiveNumericEmployeeUsers: function() {
    var usersList = [];
    var gr = new GlideRecord('sys_user');

    gr.addEncodedQuery('active=true^employee_numberISNOTEMPTY');
    gr.query();

    while (gr.next()) {
        var employeeNumber = gr.employee_number;
        
        if (!isNaN(employeeNumber)) {
            usersList.push(gr.sys_id);
        }
    }

    return "sys_idIN" + usersList;
}

[archie5]

Thanks Medi C.

With this script I am getting only one record instead of 8300.