A lookup select box variable pulls available choices from any table specified in the "Lookup from table" field.
A catalog item or order guide 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
When the lookup select box type variable is configured with an extremely large table, the system will pull in all available values from this table and display them on the catalog item order page.
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. This will evaluate the whole Incident table field and provide the available options. If the incident table grows, a user will experience slow 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 and catalog item will load significantly faster.
The performance issue is related to a large 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 to use 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. 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.