Performance Issue on Reference Qualifier

Pullela sony2
Tera Contributor

We have written one script include in global scope and calling it in Reference qualifier of Human resource:core scope.

We have one field referencing to user table in which we have reference qualifier to advanced from that we are calling script include. when we search its taking almost 3 to 4 minutes to get the users into the reference field.

 

Currently we have 60k records in the HR profile table.

 

below is my code:

 

getUsers: function() {

        var arr = [];

        var grManager = new GlideAggregate("sn_hr_core_profile");

        grManager.addEncodedQuery('u_employment_statusNOT LIKETerminated');

        grManager.query();

        while (grManager.next()) {

            arr.push(grManager.user.toString());

        }

       

        return 'sys_idIN' + arr;

       

    },

reference:

javascript: new scriptIncludeName().getUsers()

 

4 REPLIES 4

Sandeep Rajput
Tera Patron
Tera Patron

@Pullela sony2 This is expected as your reference qualifier string returns a comma separated string of sys_ids which could be containing thousands of sys_ids. Like/Contains query is resource intensive query and may take a lot of time to complete. Since you are searching in 60,000 records hence the qualifier is taking time to filter the data to show only the few records based on the filter.

 

To avoid this, you may need to add the Employment status field on your sys_user table.

 

Thank you Sandeep for replying, That's what we are thinking of. having discussion with client

Brad Bowman
Kilo Patron
Kilo Patron

60K records does not seem excessive.  If you manually filter a list view of the hr_core_profile table by the same, how many records are shown?  It is not necessary or appropriate to use GlideAggregate here - use GlideRecord instead

 

getUsers: function() {
        var arr = [];
        var grManager = new GlideRecord("sn_hr_core_profile");
        grManager.addEncodedQuery('u_employment_statusNOT LIKETerminated');
        grManager.query();
        while (grManager.next()) {
            arr.push(grManager.user.toString());
        }
        return 'sys_idIN' + arr.join(',');
    },

 

Make sure you don't have another Script Include with the same name in any scope, and you should include the scope name in the call

...new global.scriptIncludeName.getUsers()

Also consider alternate ways of retrieving the same data.  What are the values of your u_employment_status field - do you really need 'NOT LIKETerminated' or can you try using != instead, or employment_end_dateISEMPTY or even user.active=true? 

Thank you so much Brad for replying, I have tried the script and different filter still loading slow