Extremely slow query on reference field
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-22-2024 05:46 AM - edited 07-22-2024 05:50 AM
Hi,
There's a field called "financial account" (OOB name: sold product) which references a table with over 5 million records. There's also an account and consumer field. If either one of those are filled in, the financial account gets filtered based on the linked financial account of that account or consumer. Meaning, the query happens fast because it's filtered down to only a few records.
The problem is when a user wants to directly search for a financial account (example: FR454546235), it'll take forever to load (over 30 seconds) because it queries without the account/consumer filter. This is the reference qualifier script include:
getFinProductRefQualifier: function(caseRecord) {
if(gs.nil(caseRecord)) return "";
var soldProductGr = new GlideRecord(global.CSMBaseConstants.SOLD_PRODUCT);
var accountIsEmpty = true;
var isB2BConsumersPluginActive = GlidePluginManager.isActive(global.CSMBaseConstants.B2B2C_PLUGIN);
soldProductGr.addQuery("sys_class_name", "sn_bom_xxx_financial_account");
if (!caseRecord.account.nil()) {
accountIsEmpty = false;
soldProductGr.addQuery('account', caseRecord.account);
/*if (!gs.nil(caseRecord.contact) && new global.Account().isRestrictedAccessAccount(caseRecord.account)) {
var contactHasCustomerAdminRole = new global.CSHelper().userHasRole(caseRecord.contact, "sn_customerservice.customer_admin");
if(!contactHasCustomerAdminRole){
var qc = soldProductGr.addQuery('contact', caseRecord.contact);
qc.addOrCondition('additional_users', 'CONTAINS', caseRecord.contact);
}
}*/
}
//With respect to Sold Product, account and consumer are mutually exclusive until and unless B2B2C plugin is active
if (!caseRecord.consumer.nil() && (accountIsEmpty || isB2BConsumersPluginActive)){
soldProductGr.addEncodedQuery('consumer=' + caseRecord.consumer);
}
//if (!caseRecord.product.nil())
//soldProductGr.addQuery('product', caseRecord.product);
//if (!gs.nil(caseRecord.requesting_service_organization))
//soldProductGr.addQuery('service_organization', caseRecord.requesting_service_organization);
return soldProductGr.getEncodedQuery();
},
I've added a dot-walked field of financial account field called "parent sold product". This field queries very fast despite having the same millions amount of records. Even without any account/consumer filled in, it queries the right financial account within 1-3 seconds.
OOB it has this ref qual script include:
getParentSoldProductRefQulifier: function(SoldRec) {
var refQualifier = '';
if (!SoldRec.account.nil()) {
refQualifier = 'account=' + SoldRec.account;
} else if (!gs.nil(SoldRec.service_organization)) {
refQualifier = 'service_organization=' + SoldRec.service_organization;
} else {
if (!SoldRec.consumer.nil()) {
refQualifier = 'accountISEMPTY^consumer=' + SoldRec.consumer;
}
if (this.householdPluginPresent) {
var list = '';
/* if consumer present, fetch all householdIDs of which consumer is active member */
if (!SoldRec.consumer.nil())
list = this.householdUtil(SoldRec.consumer, 'consumer');
/* if Household is set, fetch all sold product for selected household as well */
if (!SoldRec.household.nil()) {
if (list.length > 0)
list = list + ',' + SoldRec.household;
else
list = SoldRec.household + '';
}
if (list.length > 0)
refQualifier = refQualifier + (refQualifier.length > 0 ? '^OR' : '') +
'householdIN' + list;
}
}
refQualifier = refQualifier + (refQualifier.length > 0 ? '^' : '') +
'sys_id!=' + SoldRec.sys_id;
return refQualifier;
},
Why is the first ref qual script include so slow? Could it be due to using gliderecord? How can I optimize the script so it queries as fast as the second one?
Any help is appreciated!
EDIT: I've already applied the tips mentioned in this thread
https://www.servicenow.com/community/developer-articles/performance-hacks-reference-fields-1-of-4-re...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-22-2024 06:11 AM
Try the first one like this (so the query conditions are all applied at once):
getFinProductRefQualifier: function(caseRecord) {
if (gs.nil(caseRecord)) return "";
var soldProductGr = new GlideRecord(global.CSMBaseConstants.SOLD_PRODUCT);
var accountIsEmpty = true;
var isB2BConsumersPluginActive = GlidePluginManager.isActive(global.CSMBaseConstants.B2B2C_PLUGIN);
soldProductGr.addQuery("sys_class_name", "sn_bom_xxx_financial_account");
// Combine all conditions into a single encoded query string
var queryString = 'sys_class_name=sn_bom_xxx_financial_account';
if (!gs.nil(caseRecord.account)) {
accountIsEmpty = false;
queryString += '^account=' + caseRecord.account;
/* Uncomment and adjust as needed for restricted access logic
if (!gs.nil(caseRecord.contact) && new global.Account().isRestrictedAccessAccount(caseRecord.account)) {
var contactHasCustomerAdminRole = new global.CSHelper().userHasRole(caseRecord.contact, "sn_customerservice.customer_admin");
if (!contactHasCustomerAdminRole) {
queryString += '^NQcontact=' + caseRecord.contact + '^ORadditional_usersCONTAINS' + caseRecord.contact;
}
}*/
}
// Add consumer condition if applicable
if (!gs.nil(caseRecord.consumer) && (accountIsEmpty || isB2BConsumersPluginActive)) {
queryString += '^consumer=' + caseRecord.consumer;
}
// Uncomment as needed for additional conditions
// if (!gs.nil(caseRecord.product)) queryString += '^product=' + caseRecord.product;
// if (!gs.nil(caseRecord.requesting_service_organization)) queryString += '^service_organization=' + caseRecord.requesting_service_organization;
// Apply the consolidated query string
soldProductGr.addEncodedQuery(queryString);
return soldProductGr.getEncodedQuery();
},
Please mark any helpful or correct solutions as such. That helps others find their solutions.
Mark
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-22-2024 08:23 AM
Hi @Mark Manders , thanks for replying!
I've tried the abovementioned code, but the query speed is still the same (around 25 sec).