reference qualifier taking a lot of time to show data

[deepika46]

Hello experts,

I have a catalog item. Provided below is the snip.

based on the 'Environment' Value, the' Key Valut Assignment (Account selection)', value populates based on the reference qualifier.

The reference qualifier script written on the ' Key Valut Assignment (Account selection)' is as follows:

var gr = new GlideRecord('u_user_profile_dev_qa');
gr.addQuery('u_active', true);
gr.addQuery('u_environment', environment);
gr.query();
while(gr.next()){
users.push(gr.sys_id.toString());
}
return 'sys_idIN'+users;

in the 'u_user_profile_dev_qa' table, for value 'prod' environment, it has more than 15 lakh records, so it is taking 40-50 secs to show value in the 'Key Valut Assignment (Account selection)' field. For other environment values, it is showing values within 2 secs as the count of records is in thousands.

Is there any way we can reduce the time or is this an expected behaviour based on the total count of records for 'prod' environment.

[Jon23]

Hi @deepika46 ,

You could try simplifying your ref qualifier.

example:

javascript: 'active=true^u_environment=' + current.variables.environment

[James Chun]

Hi @deepika46,

I am not sure if it has anything to do with the reference qualifier.

You can try applying the same filter in the backend and see how long it takes to load.

Do you have ACLs/query BR on the [u_user_profile_dev_qa] table? If your ACL/query BR is not designed correctly, it can lead to performance degradation.

Additionally, you may want to consider adding indexes to speed up the query.

You can also check the 'Slow Queries' module and create appropriate indexes to speed up the query.

Cheers