what is the best approach to populate the data in cascading drop downs

Question

Hi , 
 In Cascading drop down (based on selected country corresponding states should populate based on states cities should populate) 
 which is best approach to follow: 
 1) select the country id and pass the id to states table to get the corresponding states( here every time when i select the country i am hitting the Data base to get the states list so data base operations are more here ) 
 (or) 
 2)load all countries and states at once populate accordingly (here unnecessarily we are loading all ) 
 is there any better approach ?

aloks0189 · Accepted Answer

Hi kiranj0001 As per my understanding Approach 1 is the better approach compared to Approach 2. 
 
Because loading entire country, state & city on load will cause lot of performance issue, and also we don't want to fetch all the states unnecessary, instead of that, we need to fetch only the corresponding states of the selected Country and Cities for the selected state. 
 
Yes i agree that we need to hit the DB for multiple times, but still i believe Approach 1 is better than Approach 2

aloks0189 · Answer

Yes i agree, but Hitting the DB for 2 extra times and fetching few rows will consume less time time compared to fetching all the country, state and city on load. 
 
Let's consider, as of now we have total 195 Countries, if you consider at least 5 states for each country and five cities for each state, then number of rows which you will be fetching on load will be at least around 4875, and i am pretty sure, even it will be more than this count in real time. 
 
So as per my understanding, hitting and collecting few rows for 2 times will consume less time compared to hitting the db for once and fetching multiple 1000's of rows. And this will impact the User Experience as well. 
 
Hope this will give you a comparative difference between Approach 1 and Approach 2.