We had a reference variable where the autocomplete did not work (or at least not in any reasonable amount of time) and it took on average 13-15 seconds on a good network connection to load the results when the search window was opened. Since this behavior was well outside of every other interaction on this site, I dug in to see what could be done.
The use case was fairly simple, but it turns out the number of records and results on the tables involved were causing the slowness. The reference variable is used to select a configuration item on a server decomm request - so pick the server you want to decomm. The further requirement is that only server CIs with a certain contract were eligible for this catalog item, so the CI list needed to be filtered accordingly.
The reference variable is on the cmdb_ci_server table, with an advanced reference qualifier to return the results of a script include. The script include queries the contract_rel_ci table where the ci_item.sys_class_name = cmdb_ci_server and the contract is the specific one in question. The sysids are pushed into an array which is returned as a comma-separated string.
The issue is that the contract_rel_ci table has almost 2 million rows, with 17,000 matching the criteria, then these results were returned to the cmdb_ci_server table to be filtered again from its 91,000 records.
I came up with 2 workarounds. Neither is ideal as far as user experience, but with the autocomplete working and instantaneous search window loading, both were better than what was in place.
The first workaround is to change the reference field to a simple qualifier on cmdb_ci_server - effectively displaying every server. Once a CI is selected, an onChange script will call the script include to check if the contract is present, then a field message will display and prevent the request from being submitted if the server is ineligible for this catalog item.
I'm not a fan of letting the user take an action only to tell them they shouldn't have done that, so I kept trying alternatives.
The second workaround is to change the reference field to a simple qualifier on the contract_rel_ci table. With the related fields functionality in the condition builder, I am able to filter the results on only servers with the specific contract, so the search window is showing the same 17,000 results. The caveat to this solution is that while it may appear to the user that they are selecting a server CI, they are really selecting the contract relationship record, so in order for the workflow to function correctly, and the request to be associated to a cmdb_ci/affected CI, etc, I added an onChange script to populate a second configuration item variable that is a reference to cmdb_ci_server. This second CI variable is hidden on the request form, and the contract_rel_ci one is hidden on RITM and tasks, so this is virtually transparent to the users.
