CODEWITHSUNDEEP
javaoracle-databasedatabase-performance
oradbanj
oradbanj

Reputation: 563

Performance of database call from JAVA to Database

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 ?

Upvotes: 0

Views: 153

Answers (2)

JOTN
JOTN

Reputation: 6317

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.

Upvotes: 0

Ivan
Ivan

Reputation: 8758

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.

Upvotes: 2

Related Questions