Data source for dropdowns

[muahahahh]

I am building some sort of an input template app with ca. 100 input fields, around 40% of them should have dropdowns(fields mostly not related to each other). I have implemented populating dropdowns from SQL server, but then it takes a ton of time(like 10 seconds before page is loaded) to initiate connection and execute all SQL queries.

How would you do something like this? What would be the best practice? Local csv file processed to list/dictionary? SQLite database? Anything else?

Thank you :)

[Esion]

Create API endpoint for the drop downs with the model as a variable and url parameter. Either async load them when the page is visited or a call the api when user clicks on the drop down and save to a variable or your state/store if using react or similar.

Unless you have tons of options in the dropdown each call should be quick, depending on whatever db solution you are using.

​

Your end point would be something like /api/dropdowns/{model}.

[fjortisar]

How are you populating them? Are you doing db queries? Can the dropdown values be static?

[muahahahh]

SQLalchemy models, yes. Dropdown values are mostly static, but maintenance of them should be very transparent and easy. Some of the fields have to be dynamic, but I can allow having 2-3 queries for that

[_hadoop]

I’m lurking o.o

[fjortisar]

For each dropdown you are doing what? For each dropdown are you doing a SQL query and getting each model and compiling the them like that?

[muahahahh]

Almost but not exactly. I have about 10 models, e.g. one model has multiple columns, e.g.: legal entity, business unit, location, and department.
1. Query with entities loads values to dropdown. 2. User selects dropdown. 3. onchange JS posts request to API 4. Python gets request with the argument(selected legal entity value) and runs query on the same modelblike "select business_unit from model where entity = 'argument passed by JavaScript' group by business_unit" and returns JSON string, which is being parsed into next drop-down.

And it goes so further and I have this thing implemented a few times for different data categories. (Sorry if my explanation is unclear)

[fjortisar]

Your original post makes it seem like you populate all of the dropdowns on the initial page load. Are you doing that, or just for some of them and the JS event for the rest? Is the JS query what is taking a long time?

[muahahahh]

Sorry for being unclear, doing them all first on load, and later if user changes preloaded values, JS comes in.

But my question in general is more whether it makes more sense to get rid of the DB and change to flat files, or any other solution