Trying to improve performance with a global variable

Ignition
1

In my application, there is a tree view that shows all customers we do business with. Under each customers are locations, under each locations are areas, under areas are assets, and under assets are projects. One customer can have multiple locations, one location can have multiple areas, one area can have multiple assets, and one asset can have multiple projects.

The tree works fine displaying this and seems the most sensible way. However the performance is horrible. It queries for each customer, then for each location per customer, then each area per location etc. As you can see it grows in from n to n^2 to n^3 to … n^5 complexity, god forbid if we have to add subsections to projects and we are at n^6. Also, we have this tree on multiple screens, so each time you switch to a screen with a tree, it runs this looong query to populate the tree.

My thinking was, once at start up/login, we do this query ONCE to get the data set. That way each screen switch doesn’t have to run the monster query, it just has to look to the data set it already has in a variable. Since new customers/locations/etc can be made by anyone too, I was thinking that adding a new one would be a trigger for the global variable to refresh, if possible.

What’s the most sensible way to do this working with ignition?

2

I’d probably use a dataset memory tag and script the reloading in a gateway message handler. Any UI component that changes the hierarchy just pokes the gateway to do the refresh. All clients would get the updated dataset after the query runs.

1 Like
3

Sounds to me like your query methodology is wrong and you should be using joins.

4

You’re probably right, I didn’t make the query but it does seem very overly complicated and obviously inefficient. However between trying to break down and redo/optimize this 800 char query or just going the memory tag route, I’m going to go try the latter first.