Improve performance for pick lists based on another table

[grantfitzsimmons]

Pick lists that have records from another table as their items are very slow when the table has a lot of records (i.e, the taxon table)
Pick lists like this should probably behave like a query combo box, and only start fetching items when you start typing into them

[maxpatiiuk]

From CSIRO:

We saw that a ticket recently improved the performance of picklists, but we have one with ~10,000 values that is breaking the data entry form - it takes 10-30 seconds to load the option for the dropdown but when you click on it everything freezes and you have to close the browser tab. Were the tests done on picklists this large?
We also noticed when using the query builder that if you select an "equal" search on this field the dropdown loads fine there.
It would be nice to have the option to have something like the query combo box feature for picklists of this size - so rather than having the full list being able to start typing and have any matching values appear - would this be a reasonable enhancement request?
In the meantime are there any suggestions for changing the form to be able to cope with the large list?

[CarolineDenis]

@maxpatiiuk would infinite scrolling be a solution? Related to issue #1761

[maxpatiiuk]

Adding virtual scrolling or infinite loading to Query Combo Boxes would be really good

Then add a way to render a pick list as a query combo box (thus, pick list will gain a new feature, and this issue will be fixed for pick lists)

Also, consider changing the code so that pick lists whose items are based on another table are rendered as a query combo box by default, unless the form definition explicitly says otherwise - this is because pick lists based on another table can contain thousands of records

@CarolineDenis for more context, see Entire Table and Field From Table pick list types - https://discourse.specifysoftware.org/t/user-defined-pick-lists/205

[maxpatiiuk]

Note, as a (bad) workaround, you can limit the maximum number of items that are retrieved by a pick list that is based on a field or table using the size limit option

In the code, it's used in here:

specify7/specifyweb/frontend/js_src/lib/components/PickLists/fetch.ts

Lines 81 to 84 in 4ab1ab9

	const limit = Math.max(
	0,
	pickList.get('readOnly') ? pickList.get('sizeLimit') ?? 0 : 0
	);

Consider removing that option in the future when pick lists would be made to load items dynamically as needed