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:
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.
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:
I add a script transform on the table binding to the that query tag?
in the script transform, I read the query to a dataset
set dataset as pydataset
compose the filtering in the script
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?
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.
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})
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?
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.
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.