Identifying slow Lookup Select Box variables in the Service CatalogIssue <!-- /*NS Branding Styles*/ --> .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } Lookup Select Box variables in the Service Catalog can cause slowness when they return an excessive number of records. This slowness may be observed in the following areas: - Initial loading of a catalog item- Displaying a service catalog task (`sc_request`, `sc_req_item`, `sc_task`, etc.) where the form includes the Variable Editor formatter- Text indexing of a service catalog task A stack trace containing the following line indicates this issue: ``` com.glideapp.questionset.LookupSelectQuestion.loadChoiceList(LookupSelectQuestion.java:102) com.glideapp.questionset.LookupSelectQuestion.getChoiceList(LookupSelectQuestion.java:46) com.glideapp.questionset.AbstractChoiceListQuestion.getDisplayValue(AbstractChoiceListQuestion.java:35) com.glideapp.questionset.AbstractChoiceListQuestion.getDisplayValue(AbstractChoiceListQuestion.java:26) com.glideapp.questionset.LookupSelectQuestion.getDisplayValue(LookupSelectQuestion.java:58) ``` The `LookupSelectQuestion` keyword confirms the issue involves a **Lookup Select Box**-type variable. There are several options for improving the performance of these variables: - Convert the variable from **Lookup Select Box** type to a **Reference** type variable. This changes the user experience from a listbox to a lookup using list.- Add a more selective filter to the reference qualifier for the variable.- Use an appropriate source reference table. An inappropriate source reference table would be `task` or `incident`. An appropriate source reference table would be the Choices `sys_choice` table. For example, if an incident record producer has a variable named `category`, it is inappropriate to query the entire incident table to retrieve all category values. Instead, set the reference table to **sys_choice** and define a reference qualifier that specifies the table and field (for example, **name=incident^element=category**). Release<!-- /*NS Branding Styles*/ --> .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } All Resolution<!-- /*NS Branding Styles*/ --> .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } Navigate to **System Maintenance** > **Scripts - Background**, copy the following JavaScript snippet into the **Run script (JavaScript executed on server)** field, and select **Run Script**. ```var MAX_LOOKUP_RECORDS = 1000;var gsuri = gs.getProperty('glide.servlet.uri');var stdout = ['\n'];// Query the Variables table for var gr = new GlideRecord('item_option_new');gr.addQuery('type', '=', 18);gr.query();while (gr.next()) { try { // Check to make sure the lookup_table is defined if (gr.lookup_table) { // Determine how many records the Lookup Select Box will return var count = new GlideAggregate(gr.lookup_table); if (count.isValid()) { if (gr.reference_qual) { var encoded_query = null; if (gr.reference_qual.startsWith('javascript')) { if (gr.reference_qual.includes('current.variables')) { continue; } var evaluator = new GlideScopedEvaluator(); encoded_query = evaluator.evaluateScript(gr, 'reference_qual', null); } else { encoded_query = gr.reference_qual; } if (!encoded_query) { continue; } count.addEncodedQuery(encoded_query); } count.addAggregate('COUNT'); count.query(); var lookupRecords = 0; if (count.next()) { lookupRecords = count.getAggregate('COUNT'); } // Report a warning if the number of matching records exceeds the MAX LOOKUP RECORDS if (lookupRecords > MAX_LOOKUP_RECORDS) { stdout.push('Variable: ' + gr.name + '\n' + gsuri + 'item_option_new.do?sys_id=' + gr.sys_id + '\n'); stdout.push('\tLookup Table: ' + gr.lookup_table); stdout.push('\tReference Qualifier: ' + count.getEncodedQuery() + '\n'); stdout.push('\tThere are ' + lookupRecords + ' matching records\n' + '\tLIST URL = ' + gsuri + gr.lookup_table + '_list.do?sysparm_query=' + count.getEncodedQuery() + '\n'); } } else { // not a valid table stdout.push('Variable - ' + gr.name + '\n' + gsuri + 'item_option_new.do?sys_id=' + gr.sys_id + '\n'); stdout.push('\t*** Invalid Variable: The lookup_table value for this variable ( ' + gr.lookup_table + ' ) does not match not a valid table.\n '); } } else { stdout.push('Variable - ' + gr.name + '\n' + gsuri + 'item_option_new.do?sys_id=' + gr.sys_id + '\n'); stdout.push('\t*** Invalid Variable: The lookup_table value for this variable IS NULL.\n '); } } catch (err) { // An error occurred; report something helpful var st = err.getStackTrace(); stdout.push('Variable - ' + gr.name + '\n' + gsuri + 'item_option_new.do?sys_id=' + gr.sys_id + '\n' + 'Stack Trace:\n'); for (var i = 0; i < st.length; i++) { stdout.push(st[i].toString()); } stdout.push('\n\n'); }}gs.print(stdout.join('\n'));``` The value of **MAX_LOOKUP_RECORDS** in the script can be adjusted as needed. As a general guideline, a **Lookup Select Box** should never return more than **100** records. Example Output The script produces output similar to the following: ```*** Script: Variable - departmenthttps://<instance-name>.service-now.com/item_option_new.do?sys_id=e70ec99e53032200eb7c0a1806dc3449 There are 100002 matching records LIST URL = https://<instance-name>.service-now.com/sys_user_list.do?sysparm_query= Variable - tablehttps://<instance-name>.service-now.com/item_option_new.do?sys_id=e849da79471102003db6d7527c9a7128 There are 3189 matching records LIST URL = https://<instance-name>.service-now.com/sys_db_object_list.do?sysparm_query= Variable - broccolihttps://<instance-name>.service-now.com/item_option_new.do?sys_id=e849da79471102003db6d7527c9a7128 Invalid Variable: The lookup_table value for this variable IS NULL. Variable - meowmeowhttps://<instance-name>.service-now.com/item_option_new.do?sys_id=e849da79471102003db6d7527c9a7128 Invalid Variable: The lookup_table value for this variable (meownow ) does not match not a valid table.``` The output shows the variable name and a link to the variable definition, followed by the number of rows the lookup would return, and a link to the list of data the variable would use. The LIST URL will likely render quickly because only the first 1–20 rows are displayed; however, the variable lookup does not paginate — it retrieves all records. The output also reports errors when the lookup table is empty, invalid, or when the row count cannot be retrieved for another reason. Related Links<!-- /*NS Branding Styles*/ --> .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } Create a service catalog variable Service catalog variable attribute Types of service catalog variable