How best to run a DB query on a timed basis?

[peter]

So I have a complex UDT that represents a machine and aggregates data from different locations at different times. I have OPC data being collected whenever OPC collects data, and I have some DB data that can be collected on triggers based on the OPC data.

However I have some DB data that I need to insert into each UDT instance on a timed basis.

I know I can attach a script to a gateway timer event, but that would mean editing that script each time a new UDT instance is created. Preferably I would like a time event attached to the UDT itself, but that doesn’t look possible.

My fallback would be to add a timed trigger in the machines themselves, and use a tag Value Change event i the UDT. But this seems ugly to me.

Are these the only choices that I have?

Note that instances of the UDT will be added to the system in dribs and drabs over time by the client themselves - so I am trying to build a robust system that can easily be managed by not the smartest people in the room.

[lrose]

SQL Bridge is an option if you have that or are willing to purchase it.

Personally a timed trigger and a Gateway Tag Change script is fairly clean. When a new UDT is created you would only need to subscribe the new tag to the event (assuming the query doesn’t change).

You could potentially add a flag to the UDT definitions. In the gateway timer event, browse through the UDT’s and any that have the flag marked as true need to have the query ran against them. This would allow for new UDT’s to be created without modification of the script (again assuming the query doesn’t change).

I suppose it would also be possible to include the query you wanted to run in a UDT instance, but depending on who has designer access this could be less than desirable.

I would stay away from value change scripts, especially if you’re going to have very many or if you’re going to be attempting potentially long running scripts. There is a limited thread pool (I think the default is 5 threads) for Value Change event scripts and once it’s full you’ll start to miss events.

[peter]

@lrose

SQL Bridge is an option if you have that or are willing to purchase it.

I have it, but I am trying to avoid it as my understanding is that Transaction Groups can't be parameterized. Which means that for every UDT instance a new TG will have to be created. And I need to pick a small subset of fields out of a large table. This greatly increases the potential for errors in creating queries as there will upwards of 100+ UDT instances slowly created over time by the client themselves, and they are not SQL and/or HMI experts.

I do understand that Named Queries could be used in scripts to parameterize the queries themselves.

Personally a timed trigger and a Gateway Tag Change script is fairly clean. When a new UDT is created you would only need to subscribe the new tag to the event (assuming the query doesn't change).

Can you elaborate on this? Who is subscribing to whom?

You could potentially add a flag to the UDT definitions. In the gateway timer event, browse through the UDT's and any that have the flag marked as true need to have the query ran against them. This would allow for new UDT's to be created without modification of the script (again assuming the query doesn't change).

All UDTs will have to be updated with current information from a particular table at basically the same time. I am confused as to who is setting the flag in the UDT. Are you suggesting the machine sets the flag, and the GW timer script polls all the UDTs and runs the query? (if so, how does the GW script know the list of UDT instances?)

FWIW Yes, it is the same query for all machines, just with each machine's ID in the WHERE clause

If there is a way to automatically get a list of variables that are of a particular UDT, then I could iterate that and do everything on a GW timer script. That is looking like to be the best option if it is possible.

edit I just found system.tag.browse(), so that may solve my problem.

I suppose it would also be possible to include the query you wanted to run in a UDT instance, but depending on who has designer access this could be less than desirable.

I was trying to keep the UDT as the source of all truths, rather than scatter things in different locations.

I would stay away from value change scripts, especially if you're going to have very many or if you're going to be attempting potentially long running scripts. There is a limited thread pool (I think the default is 5 threads) for Value Change event scripts and once it's full you'll start to miss events.

I currently have 2 value change event scripts per UDT, with the potential 100+ instances. However, the rate of execution of those scripts is only about once every minute. This is the only data in the entire system (no visualization at all) so I hope that I won't overtax that thread pool (can you change that default?)

Note that in addition to the reads per UDT, I will also need to write data out to two different tables. One table is supposed to be written every second, while the other every minute or so based on a value. After working through all of the above I'm guessing that putting the "every second" write in a GW timer script would be the best thing to do, and perhaps put the other update in a Name Query for execution off a value change event.

[lrose]

@peter

Can you elaborate on this? Who is subscribing to whom?

When you create a Gateway Tag Change Script you add the tags that the event should be subscribed to. See Tag Change Scripts for a better explanation. Basically, any tag that the event is subscribed to will cause the script to run.

The triggering tag value and path are available from the script so it is a fairly simple task to write a generic script that will execute your query using the specific triggering tags information.

@peter

I currently have 2 value change event scripts per UDT, with the potential 100+ instances. However, the rate of execution of those scripts is only about once every minute. This is the only data in the entire system (no visualization at all) so I hope that I won’t overtax that thread pool (can you change that default?)

It's hard to say and a lot of it has to do with how intensive the scripts are. I learned this lesson the hard way and spent about a month or so converting numerous valueChange scripts over to Gateway Tag Change Scripts.

Don't misunderstand, valueChange scripts have their uses, but most of the time Tag Change Scripts are superior.

This will provide some understanding on the topic.

Tag Change Missed Events

Another tag script performance piece that may be affecting OP is that there is a dedicated thread pool size 3 that all tag change scripts are executed on. So in addition to having a per-tag queue of size 5 that events will sit in, there’s only ever 3 tag change scripts executing at once. This is where you can really get into trouble with long running scripts (as @pturmel hinted), because if you have 3 long running scripts then nothing else is executing until at least one of them finishes.

[peter]

That all sounds like a plan! Thanks!