# Query tag to alleviate some query workloads on perspective pages **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [May 6, 2022, 3:16pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302 "2022-05-06T15:16:57Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 6, 2022, 3:16pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302/1 "2022-05-06T15:16:57Z")
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. ---
**Author:** ![obober](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/obober/32/67865_2.png) [@obober](https://forum.inductiveautomation.com/u/obober)\ **Post date:** [May 6, 2022, 9:38pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302/2 "2022-05-06T21:38:29Z")
> 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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/1/f1512f86916ff15a4701b9eeb9690d5f714a0b75.png) 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. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 10, 2022, 1:16pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302/3 "2022-05-10T13:16:09Z")
editing my post because I didn't get more responses, so maybe too wordy? > [@obober](#): > > Could you elaborate on how you expect to alleviate query workload on Perspective pages using a query tag? 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? ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 10, 2022, 3:49pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302/4 "2022-05-10T15:49:20Z")
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 ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [May 19, 2022, 3:44pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302/5 "2022-05-19T15:44:01Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 19, 2022, 4:02pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302/6 "2022-05-19T16:02:58Z")
> [@bkarabinchak.psi](#): > > expression like `view("SELECT * "+{view.props.WhereClause}, {theMasterDataset})` 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: ```python view("SELECT * WHERE someColumn==args[0]", {theMasterDataset}, {theColumnValueToMatch}) ``` ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 23, 2022, 1:53pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302/7 "2022-05-23T13:53:42Z")
If my tag query is this: ```auto 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 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 23, 2022, 1:59pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302/8 "2022-05-23T13:59:45Z")
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. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 23, 2022, 2:04pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302/9 "2022-05-23T14:04:07Z")
I am attempting the following, but the error says incorrect syntax near ‘07’. ```auto 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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 23, 2022, 2:11pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302/10 "2022-05-23T14:11:25Z")
> [@pturmel](#): > > require quote marks ```auto '{[~]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. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 23, 2022, 2:30pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302/11 "2022-05-23T14:30:31Z")
`'{[~]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 ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [January 6, 2023, 6:59pm UTC](https://forum.inductiveautomation.com/t/query-tag-to-alleviate-some-query-workloads-on-perspective-pages/59302/12 "2023-01-06T18:59:51Z")
> [@bkarabinchak.psi](#): > > A query tag that every perspective client draws from is not a bad idea. 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.