Service Catalog | Troubleshooting - A lookup select box variable points to an extremely large table causes performance issues when catalog item loads - Support and Troubleshooting

Service Catalog | Troubleshooting - A lookup select box variable points to an extremely large table causes performance issues when catalog item loads Issue  A lookup select box variable pulls available choices from any table specified in the "Lookup from table" and "Lookup value field" fields.

A catalog item, order guide, or any place where variables are displayed, with a Lookup Select Box type variable may experience these symptoms:

Catalog item takes a long time to load (same on Service Portal) Order Guide on Service Portal takes a long time to load Catalog item on an order guide takes a long time to load Release All releases

Cause When the lookup select box type variable is configured with a large table, the system will pull in all available values from this table and display them on the catalog item order page. The max number of choices that can be displayed has a hard limit of 10,000, but that limit only protects against extreme performance degradation; for example, keeping the instance from running out of memory. The limit will not stop the variable from causing slow form loads. Depending on how many records match the reference qualifier of the Lookup Select Box, the response time will scale linearly. For example, in one case, a Lookup Select Box pointed at the CMDB table with 1 million records caused the form to take over 2 minutes to load on average.

In most cases, the available values are available from the Choices [sys_choice] table. The best practice is to use the Choices table whenever possible. Otherwise, use reference qualifiers to control the number of values returned.

For example, the Incident table has a preferred contact type field with choices like email, phone, web. A lookup select box variable was used in a catalog item with Incident table as the "lookup from table" field and preferred contact type as the "Lookup value field" field. This will evaluate the whole Incident table (potentially millions of records) to determine and then provide a handful of available options. If the incident table grows, a user will experience increasingly slower performance. In this case, it is better to refer to the Choices table where the options for the preferred contact type field of the Incident table is stored. There will be only 3 options that need to be evaluated and the catalog item form will load significantly faster.

Resolution The performance issue is proportional to the number of results that have to be retrieved and displayed on the screen. Control the number of results by using reference qualifiers to help lower the data retrieved.

Questions to ask:

Are all the data retrieved needed? If not, what reference qualifier can be used to retrieve only the needed data (a subset of data)?

Are the values available from the Choices [sys_choice] table? If yes, use the Choices table instead of directly querying a field from a large table. The data retrieved from the Choices table will be significantly smaller.

Can Select Box type be used or Multiple Choice type be used instead of a Lookup Select Box type? Any of these alternatives will help improve the performance on a Lookup Select Box variable type.

For other troubleshooting articles related to performance, click here .

For documentation of the Lookup Select Box variable type see https://www.servicenow.com/docs/csh?topicname=r_VariableTypes.html&version=latest