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
We had a similar problem, where some developer thought it was a good idea to gather the entire dataset and filter through it after the fact, though in our case not for any reason nearly as practical as what you have suggested.
Your case is an attempt to avoid hitting the DB connection pool 3 times when you could do so only once. That may be a great plan, but there may be a significant downside to doing it the other way.
The PM in question, where we were gathering the entire dataset, was the absolute worst thing ever on our Appian health check, because each PM was storing basically the entire database table for 7 days before archiving. We didn't notice until the records were hitting the several thousands and we were continually RAM spiking and having to restart the servers.
The only problem I see with doing 3 queries is that you're running the risk of having a large part of the table, a subset of that, and a subset of that all stored in RAM. Depending on how big your table and subsets get, this could be more crippling to your engines. If a finite number of possibilities for level 1, level 2, and level 3 exist, you might try waiting until the last to run the query at all, perhaps using expression rules or Appian decision objects to filter down to avoid 3 queries.