0

Our team is building a small application wherein a UI has about 10 drop-down list boxes. ( DDLB ).

These list boxes will be populated by selecting data from different tables.

Our JAVA person feels that making separate database call for each list will be very expensive and wants to make a single database call for all lists.

I feel it is impractical to populate all lists in one database call due to following reason

 a. Imagine an end user chooses state = 'NY' from one DDLB.
 b. The next drop down should be populated with values from ZIP_CODES table for STATE='NY'

Unless we know ahead of time what state a user will be choosing - our only choice is to populate a java structure with all values from ZIP_CODES table. And after the user has selected the state - parse this structure for NY zipcodes.

And imagine doing this for all the DDLB in the form. This will not only be practical but also resource intensive.

Any thoughts ?

Improve this question
1
  • 1
    Feelings are not the same as empirical evidence. How about benchmarking ten focused database calls vs one ginormous database call? Alternatively, remind your Java person that premature optimization is the root of all evil and suggest that you start with the simplest thing which could work, (i.e. one call per list), and refactor later on, if necessary. Commented Jul 26, 2018 at 6:45

2 Answers 2

Reset to default
2

If there are not many items in those lists and memory amount allows you could load all values for all drop boxes into memory at application startup and then filter data in memory. It will be better then execute SQL query for every action user makes with those drop boxes.

You could also use some cache engines (like EhCache) that could offload data to disk and store only some fraction in memory.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@oradbanj, It wasn't me who downvoted but maybe it was downvoted as primarily opinion-based since this is your Java guy who says something and there are no technical details in the question (like average amount of items in every drop down, memory limitations for your application and so on)
0

You can run some timings to see, but I suspect you're sweating something that might take 100th of a second to execute. UI design wise I never put zip codes in selection menus because the list is too long and people already know it well enough to just punch in. When they leave the zip code field I will query the city and state and pre-fill those fields if they're not already set.

Improve this answer

2 Comments

@Ivan - let us assume that there is enough memory to support loading say 10 DDLB (dropdown list boxes). Are there any techniques available which can provide contents of multiple tables ( both structure and data ) in a single variable ?
@oradbanj I'm not sure, but if you are caching all data only at application startup you can execute 10 separate queries (maybe even in 10 parallel threads).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.