Is it possible to have my gateway run a system.db.refresh?

Here’s the situation. I have a long query (you may remember my post from last week, well I was unable to optimize despite all attempts and I need a solution even if temporary) that takes a long time to load, which really slows down our program.

Instead of referencing the query directly, I’m making a historicTable with the results of it (the table isn’t updated more than a few times a day anyways). When any of the underlying tables is UPDATED, INSERTED, or DELETED from, I’m setting up MySQL to recreate the table.

Now this is what I am wondering - one client edits or updates one of the tables, and my historicTable gets recreated. Is there any way for the gateway to tell all the other clients to now refresh that table (if they’re on the screen)?

You can use system.util.sendMessage() to broadcast from your gateway to all of your clients. The message handler in the client would check if the affected window is open and invoke the refresh if it is.

1 Like

In locations where clients edit or update one of the tables, the script that updates the tables could also write a timestamp to a tag (update_historicTable). Then add a where clause (WHERE ‘{update_historicTable}’ = ‘{update_historicTable}’) to the components that query the historic table.

Note, that if it takes a long time for historicTable to be recreated, then whatever method you use would need to delay long enough for the table to be recreated.

If you can decide from the server when to update the data, it’s probably not very dependend on the GUI state (no timer sliders to select a time range f.e.)

In that case, it’s probably an ideal candidate for named queries. Named queries can be cached on the server, and the cache can be removed at any point. Do note that caching is only good if the same results will be fetched more than once (f.e. to different clients or over a certain time).

1 Like