Just as a point of reference, note that the tag history tables have a sqlth_te table that stores tag paths and ids, and these ids link to the data tables. There could be thousands of tags, but history still comes back super quickly despite having to do a join onto the tag table.
You also want to make it easy for future engineers who come along and have to make changes to the system
2 Likes
I understand now. So the query itself isn't actually what you are trying to optimize just how often it needs to be called etc.
I would still recommend what @nminchin said about a table. For instance, if you need that relationship anywhere else, you will need to hardcode it again and this violate the DRY principle. Now imagine you wrote it in two or three different places, and now they add a machine - instead of just inserting that relationship into a table, you must now find every where you hardcoded it and then change it.
You also want to make it easy for future engineers who come along and have to make changes to the system
Unless performance is critical and you can't get it in a readable way, this should always be the goal imo. Because that person often ends up being yourself anyways.
1 Like
I could have the dropdown use a query tag that is populated from such a query.
Then instead of the script getting a number from the dropdown to get the string of the machine, I can get the string, and compare it directly. Thanks
But then instead of running the query only when needed, you’ll be running the query every x seconds
how?
A query tag is polled
I thought the query tag would only be checked when the page opens initially, and if the query tag driven/fixed rate event was updated.
Also, I set it to update each 8 hours.
Did I misunderstand how query tags are polled?
A query tag , like any* tag, uses a tag group to update. You can’t check if results from a query have updated without running the query 
I didn’t set it for tag group though. I set it for fixed rate.
I will get a picture, one sec.
This one is set for event driven
Then I used a parameter that updates with the shift in the query the way that I saw in another thread.
like this:
'{[~]Shifts/Current Shift Start}' > '2022-01-25 07:00:00'
What do you have for the top basic props section?
Ignore the red highlight, screenshot from user manual
query
oh you mean tag group default?
the execution mode isn’t tag group though right? so it will only update on the driven execution?
Hmm, so what’s your query? An event driven tag will only update if the expression/query changes e.g. by referencing something dynamic which will trigger the tag to re-evaluate
I’m not sure what you meant by this
'{[~]Shifts/Current Shift Start}' > '2022-01-25 07:00:00'
‘{[~]Shifts/Current Shift Start}’ > ‘2022-01-25 07:00:00’
That is always a true statement in my where clause. It was just a way to get that tag in there which updates every shift start.
I think the query is pretty slow, like maybe 6 seconds long.
I know I can clean it up too. I don’t think I need the subquery. I think at one time, it was more complex, and that is a remnant.
I also have to remove the subquery to filter the code value dynamically with the script.
select subq.Line,Year_Month, Stops, highInfeed-lowInfeed as Attempted, highOutfeed-lowOutfeed as Palletized
from (
select min(t_stamp) as earliest, FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, coalesce(count(Code),0) as Stops,Line,
min(infeed) as lowInfeed, max(infeed) as highInfeed, min(outfeed) as lowOutfeed, max(outfeed) as highOutfeed
from mytable
where (t_stamp>DATEADD(month, -6,EOMONTH( GETDATE()))) and (Code<1002)
and '{[~]Shifts/Current Shift/Current Shift Start}'>'2022-01-25 07:00:00'
group by FORMAT(t_stamp, 'yyyy-MMM'),Line
) subq
order by subq.Line,earliest desc
Just to reformat so you (I?) can more easily read it:
SELECT
subq.Line,
Year_Month,
Stops,
highInfeed-lowInfeed as Attempted,
highOutfeed-lowOutfeed as Palletized
FROM (
SELECT
MIN(t_stamp) as earliest,
FORMAT(t_stamp, 'yyyy-MMM') as Year_Month,
COALESCE(count(Code),0) as Stops,
Line,
MIN(infeed) as lowInfeed,
MAX(infeed) as highInfeed,
MIN(outfeed) as lowOutfeed,
MAX(outfeed) as highOutfeed
FROM
mytable
WHERE
t_stamp > DATEADD(month, -6,EOMONTH( GETDATE())))
AND
Code < 1002
AND
'{[~]Shifts/Current Shift/Current Shift Start}' > '2022-01-25 07:00:00'
GROUP BY
FORMAT(t_stamp, 'yyyy-MMM'),
Line
) subq
ORDER BY
subq.Line,
earliest desc
1 Like
I write the other way so that I can see the whole query better in the designer, sorry.
That is easier to read on the forums, but in 8.1.0 designer, that is not easier for me.
I think tomorrow I will use the tips from Pascal to filter the code values with the expression structure binding.
I need to unpack that subquery.
I also need to use the tip from you and psi to populate that dropdown from a query tag.
Then I need to pass a string from my dropdown instead of an integer.
Thanks for helping me
Is it ever better to pass an integer to a query and using case statements to convert the incoming integer to a string within the query?
Or was that just like a bad habit I developed from trying to move data fast through J1939, sending the smallest amount of bits I could?
I’ve been doing a fair amount of refactoring/optimizing queries as well.
What database is this in? If you run an explain plan on it, are there any red flags? A query that takes 6 seconds might be better off getting optimized on the pure database level rather than trying to squeeze out performance on the Ignition side.
For instance, does the table mytable have an index on the t_stamp and Code column?
2 Likes
Sounds like you need a bigger monitor! I use a 2560? At work and a 4K at home. Monitors are CHEAP! You easily pay for it in a few hours that someone would have spent trying to work out what's going on from poor formatting
Again, think of future engineers. If it were me as the future engineer, the first thing I would do is to reformat that query and save it
1 Like
I have held off on indexing.
I think you might be undervaluing the performance improvement calling the query once per shift instead of once per session. I might be overvaluing it though.
I appreciate the input.
I will try to make my code more readable in forum posts and in the project.
Monitors
A little off topic, but I use dual 1080s size 23" usually at home and at work.
At work I also have the laptop screen as well though.
I find that particularly if I am drafting a system in acad, then I miss more things when my screen is larger. Everyone is different though, and I had a coworker with a giant TV for a monitor who always printed his drawings and had a thorough inspection process involving coming back to the review the next day fresh among his steps.
I then at home have a projection system connected for watching movies on a larger screen with higher resolution. For working and gaming, I like to be able to see as much of the screen as I can without turning my head, and I stream which honestly, sometimes I struggle to stream a few things on even 1080. Way off topic though, and mileage varies.
Way off topic though.
Well you might want to index... I don't know of any real downside, and it will only increase the performance coming from the Database. But I'm not a DBA so YMMV.
2 Likes
I don’t know.
I read that when you add indexing, the inserting of new data is slower.
I am not sure if it is an outdated thing, but people used to have an agile table unindexed where inserting came very fast.
Then they had an archive table of months past that was indexed.
I am in a situation where I kind of am building a ship in a bottle too. I don’t have many permissions in the project as I work on just one site’s project. I think this has made me a little avoidant of implementing software to look at the database.
It sounds like you index your tables and don’t observe insert issues. How fast do you collect new data to your tables and how much data?