Improving advanced reference qualifier performance

[Anil Lande]

Reference qualifiers are used to apply query on fields/Variables  of type reference in ServiceNow.

To learn more about reference qualifier you can check below Article by Gaurav, he has given many links and references with different example in his article: Reference Qualifier

If we have complex queries want to make reference qualifier depending on other fields/variables on form, or based on logged in user profile we use advanced qualifier. In most of the cases we prefer to use script include function call and return query like sys_idINSYSID1,SYSID2,...,SYSIDN.

Return such query form reference qualifier is not affecting performance in simple queries. Or in some cases where the table is contains very few records.

But if you look closely how it works in below example:

Show users who belongs to the same department as the logged in users department.

getDepartmentUsers: function() {
var userRec = new GlideRecord('sys_user');
userRec.get(gs.getUserID());
var department = userRec.getValue('department');
var usersList = [];
var grUser = new GlideRecord('sys_user');
grUser.addQuery('company', gs.getUser().getCompanyID());
grUser.addQuery('department', department);
grUser.query();
while(grUser.next()) {
usersList.push(grUser.getUniqueValue());
}

return 'sys_idIN' + usersList;
},

In this example we have reference field referring to sys_user table and we using GlideRecord and return list of users (lets say 1000 users). Basically we are preparing a query with 1000 sys_id's for that we have used while loop which will execute 1000 times. You can imagine if the execution time if records are in lacs.

This is will not stop here, the returned query (a big query string) is again being applied to filter data from reference field to filter data and represent it to end user.

In this case we not considering any before Query business rule execution time.

So instead of using GlideRecord query to get sys_is's in script include we can return the encoded query. Output will be same and it will skip unnecessary execution of while loop.

To improve the performance we can convert this like:

getDepartmentUsers: function() {
var userRec = new GlideRecord('sys_user');
userRec.get(gs.getUserID());
var department = userRec.getValue('department');
var usersList = [];
var query = 'company='+gs.getUser().getCompanyID()+'^department='+department;
return query;
},

Below are few examples which were facing performance issue because of similar implementations, and issue were resolved by adapting the suggested way. Also these examples motivated me to write this article 🙂

reference qualifier is returning data too slow

Lag when Incident is being searched in a field. Does reference qualifier cause lag?

So, we should always prepare a query and return it to the calling function instead of collecting sys_id's for another query to be applied on same table again through reference qualifier.

Note: Please make note that this will help in those cases where we want a data from same table on which reference qualifier is applied. You can improve most of your reference qualifiers where you are returning a query like sys_idIN<comma separated sys_id's> or just return comma Separated sys_id's. 

Also you can have a look on below links to improve performance of Reference Fields:

Performance Hacks: Reference Fields (1 of 4) Reference Popup / Reference Lookup 

Performance Hacks: Reference Fields (2 of 4) Autocomplete Wait Time

Performance Hacks: Reference Fields (3 of 4) The Autocomplete Query

Performance Hacks: Reference Fields (4 of 4) Contains vs. Starts With

Kindly mark this article as helpful if this helps you to solve your requirement/improve performance. Let me know if you have any queries or want to suggest any changes.

Thanks,

Anil Lande

[mamann]

Just a note, if you're in the global scope, you can use gs.getUser().getDepartmentID() and bypass the need to look it up from the User table.

[Anil Lande]

Yes, you are correct. I missed that while preparing an example.

We can use gs.getUser().getDepartmentID() to get user department.

Thanks for the input.

[Wardma]

The content here is great, pretty straight forward. Im trying to perform a similar task by filtering a list collector on the location table to return the countries only and remove any duplicates of said countries. 

I then want to try to filter a second list collector based on the items selected in the first list collector to return locations associated with those countries. 

Im having difficulty understanding filtering and displaying only the countries on the first list collector. Which ultimately leads to not being able to provide data in the second list collector. 

I want to be able to select multiple countries in one and then only provide the relevant items for those in the second that are active and do not contain a location type of conference room.