Query tag to alleviate some query workloads on perspective pages

I think I want to alleviate some of the query workload using a query tag.

However, at least in Ignition 8.1.0, the execution mode is complicated as I look at it.
I have transaction groups that happen at the end of a shift.

How do I set the execution mode to match that?
I have event driven, fixed rate, and tag group options.
I think event driven is the one, but I am not sure how to use it.

Manual says: * Event Driven - Updates when something happens (i.e., value event or alarm event) within the expression.

Blockquote I think I want to alleviate some of the query workload using a query tag.

Could you elaborate on how you expect to alleviate query workload on Perspective pages using a query tag?

Blockquote However, at least in Ignition 8.1.0, the execution mode is complicated as I look at it.
I have transaction groups that happen at the end of a shift.

The Event Driven execution mode re-executes the query whenever any of the tag parameters in the query change. For example:
image
The query on my tag query test will update whenever the value of tag number changes.

Blockquote How do I set the execution mode to match that?

You could have a tag that denotes the end of a shift, and include that in your query. That way, the query in the query tag will update whenever the tag updates as well.

1 Like

editing my post because I didn't get more responses, so maybe too wordy?

I have queries running when people open perspective pages.

  1. A tag query could run once a day, and populate the tables for my pages when the pages open?

  2. in your example, @obober you are using a tag as a parameter?

  3. Do you just put the tag path in the curly brackets?

  4. query updates when that tag updates?

I think to improve performance:

  1. create a perspective page with a table.
  2. Bind the table to query tag
  3. how do I write the query tag’s query to contain a tag that controls the driven part?
    a. I write the path in curly brackets?
    b. that will make the query update when that tag updates?

To filter results with a dropdrop:

  1. I add a script transform on the table binding to the that query tag?
  2. in the script transform, I read the query to a dataset
  3. set dataset as pydataset
  4. compose the filtering in the script
  5. then return a dataset of the filtered query?

Is this creating polling that defeats the whole purpose of using the query tag instead of a regular query?

@bkarabinchak.psi from that other thread, I made this thread

I think maybe too wordy, no responses, I revised the posts to be shorter
I don’t really know what “{[~]number}” means

It’s been a minute since I’ve worked with perspective but my thoughts if you’re ultimate goal is to reduce the number of times your db is hit by Ignition -

A query tag that every perspective client draws from is not a bad idea. If you don’t have this on polling, somewhere in your logic, something must tell it when the “Right” time to refresh is, but even if you have it on polling that seems ok - whether you have 1 or 10 or 100 perspective clients, only the Gateway is running the query for the Query tag 1 time, so you’ve made it so that increasing how many clients you have doesn’t increase the database queries required. I personally wouldn’t mind putting it on polling since you’ve eliminated the bigger issue of more clients making more queries.

Alternatively instead of a query tag, you could make it a named query with cache-ing enabled and I think performance wise it would probably be the same but don’t quote me on that. If the cache was cleared and you have 100 clients connected, the first one to ask the gateway for the data would trigger a re-running of the query, but the other 99 would then be able to get it from the cache’d result (assuming this isn’t a table that updates often). If it is a table that updates often, I wouldn’t bother with trying to optimize the cachei-ng and would just put it into a query tag tbh.

For filtering - you could do all of it without needing to hit the DB again if you do it via jython as you suggested, manually filtering the dataset in a transform. You could also use @pturmel’s Simulation Aid’s module, your dropdowns would form the WHERE clause of the psuedo sql and then you’d have an expression like view("SELECT * "+{view.props.WhereClause}, {theMasterDataset}) - and this is what you would bind your tables data too. I would opt for this way tbch - it’s much easier to get working and cleaner to read than scripting imo.

Again, I haven’t touched perspective in a while, so I am probably missing some idiosyncrasies that would help or hurt performance. I don’t know for instance if getting data from a dataset versus getting it from a named query cache is faster or slower, I am assuming it’s about even but I can’t say that for sure.

1 Like

You should try to avoid changing the Pseudo-SQL passed to view(), as it has to be reparsed whenever it changes. Use the args tuple to pass values to your WHERE clause. Like so:

view("SELECT * WHERE someColumn==args[0]", {theMasterDataset}, {theColumnValueToMatch})
1 Like

If my tag query is this:

select Line, coalesce(count(code),0) as Stops
from myTable
where (t_stamp >dateadd(day,-1,convert(datetime,convert(date,getdate())))) and code <1002
and and {[~]Shifts/Current Shift Start}>0
group by Line
order by coalesce(count(code),0) desc

when the tag current shift start changes, the query will run?

errr that is throwing errors

Curly braces perform string substitution. Date/time objects generally require quote marks. Or, often, require string formatting into another tag in a DB-acceptable format.

1 Like

I am attempting the following, but the error says incorrect syntax near ‘07’.


and {[~]Shifts/Current Shift Start}> '2022-01-25 07:00:00'

*even if I remove the time

and {[~]Shifts/Current Shift Start}> '2022-01-25'

What I am trying to do is add a tag that will act as a dummy filter so that the tag drives the event of my query running when the shift changes.

'{[~]Shifts/Current Shift Start}' > '2022-01-25 07:00:00'

But, still might not work because the date/time will be formatted using java's style.

Consider not using a query tag. Use a memory tag, and use a gateway tag change event on shift start to run a PrepQuery. That can use ? substitution to avoid string conversion. Write the result dataset to the memory tag.

2 Likes

'{[~]Shifts/Current Shift Start}' > '2022/01/25'

This worked, thanks.

edit: tested and

'{[~]Shifts/Current Shift Start}' > '2022-01-25 07:00:00'
worked as well

I ended up scrapping the idea.
Transfer speed and missed fixed rate updates were the leading reasons.

Made the queries more efficient.
I think I also checked the virtualized box in the tables.
Enabled caching I think like you said.

In a couple of cases, I bound limits on the difference between lower and upper bound dates from the date pickers.