Every query comes from the same database but a few different tables. My UI elements are placed all over the screen and cells next to each other are not necessarily displaying information from the same table, and some cells will always be empty. The cells displays data about a product which the users can change in a dropdown menu. So how should I go about showing the data from my Named Queries?
Each cell is a label with a query binding that either;
queries the whole table and then using a transform to display the right value from the query result, or
queries only the correct column based on a parameter in the named query
Try to merge the labels into table components instead and bind the table to a SQL query with UNION:s to display the data correctly
Using query tags (UDT or folder with tags) which update when the dropdown menu is used
Using a script to populate memory tags (UDT or folder with tags)
Another way?
What I'm concerned about is bandwidth to the SQL database. It's probably not an issue but there are many programs which queries this database so I want to keep my queries to a minimum.
I have seen that you can cache query results, both directly in the named query and in the query binding of a component. The problem is then if I use a named query with parameters, the caching is not helping.
Right not I have 35 cells which queries data from 3 different tables so in my mind it's better to do three "big" queries instead of 35 small ones.
I want to do it the "right way" from the beginning so it's easy to add more fields later on if I have to and not make my program too heavy/slow to run.
I would recommend, creating a property (or multiple) which holds the raw data. Only filterd by parameters which are the same for all data (e.g. a date range). Then I would use expression bindings with a lookup expression to filter and retrieve the appropriate cell data from that property.
Also, If you haven't yet and you have the ability to add a 3rd Party module, I would strongly recommend that you look into Automation Professionals' Integration Toolkit Module. It has a whole array of extreamly useful expression functions which are geared towards exactly this type of use case.
I would add that you could consider the use of the Flex Repeater component to show the data rather than using a table. You might find it easier to pass a simple parameter into each view rather than trying to munge the data into tabular format. The only problem I foresee is that you'll have to control the number of columns by setting their width relative to the Repeater's width. e.g., 33%.
If possible, I would try very hard to issue a single query (parameterized as needed). Allow the SQL DB to do the hard work, return the table of data in the format that effectively communicates the end result (empty 'cells' included).
I appreciate your attention to reducing the number of queries for data, as I feel this is often overlooked. If a) the data is not expected to update periodically (tag history, etc.), or b) if you have multiple drop downs (especially when one drop down depends on another) - consider adding a button which issues a single query after all dropdowns are selected by the user. If the table does not need to update automatically (periodically), then disable that feature.
I believe the table component can be very performant when working with large datasets, so I often use the table component. It is easy to add search & filter functionality, which is a common request. When the columns need to be dynamic / selectable, I place a binding on the columns object and generate the columns dynamically. - typically, from the same 'thing' that is generating the dynamic SQL.
