Should I Perform Multiple Queries or Just One and Sift Through the Dataset?

Suppose I have a large table in the database with some number of columns, and Im interested in 4 of them: id, level1, level2, and level3. 

While id is a unique identifier for each row, so is the combination of level1, level2 and level3. 

I want to create three dropdowns:

The first one chooses from available level1 values.

Given the choice in the first dropdown, the second chooses from corresponding level2 values.

Given the choice in the first and second dropdown, the third chooses from corresponding level3 values, and saves the corresponding row id. 

Currently I am performing a new but smaller query every time a choice is made in any of the three dropwdowns. Is this the more efficient way? Or is there a nicer option where I do one large query of everything at the start, and slowly filter out using the information I get from each dropdown? If the latter is more efficient, what would be the best way to implement this?

  Discussion posts and replies are publicly visible

Parents
  • Certified Lead Developer

    It kinda depends on the expected size of the table in question.  If it's a lookup table where the size will never grow (or at least, never grow much), then it may be worthwhile (if slightly) to query the whole set and divide up the queried dataset on-form as it sounds like you're already thinking of.

    The downside here is, it seems like it might be quite a bit harder to maintain if there are ever any data or structural changes down the road - and additionally, unless the query is particularly expensive in terms of performance, I wouldn't expect this method to be necessarily all that much more efficient in total than doing smaller, column-limited queries at each level of user input.  Plus consider that when it's set up this way, with multiple smaller queries, the query load on the system is more spread out considering that when a user first loads the form, it just does the smaller initial query, and then subsequent queries are only done when (and if) the user drills down.

Reply
  • Certified Lead Developer

    It kinda depends on the expected size of the table in question.  If it's a lookup table where the size will never grow (or at least, never grow much), then it may be worthwhile (if slightly) to query the whole set and divide up the queried dataset on-form as it sounds like you're already thinking of.

    The downside here is, it seems like it might be quite a bit harder to maintain if there are ever any data or structural changes down the road - and additionally, unless the query is particularly expensive in terms of performance, I wouldn't expect this method to be necessarily all that much more efficient in total than doing smaller, column-limited queries at each level of user input.  Plus consider that when it's set up this way, with multiple smaller queries, the query load on the system is more spread out considering that when a user first loads the form, it just does the smaller initial query, and then subsequent queries are only done when (and if) the user drills down.

Children
No Data