I have this application where data is updated on the text label on the screen as operator scans new parts for a stack of 8. Currently, each label has a 1 sec polling named query and I have 25-30 such screens with about 10 queries per screen which seems to be pushing limits.
I came up with a named query with several select statements with an UNION binded to a custom prop.
Select COALESCE(SUM(serial), 0) from parts where layer = :layer and layer_pos = :layer_pos and pallet = :pallet and stack_pos = 1
UNION
Select COALESCE(sum(serial), 0) from parts where layer = :layer and layer_pos = :layer_pos and pallet = :pallet and stack_pos = 2
UNION
etc..up to stack_pos=8
Say the first part is 1 and gets into Dataset[1,0]. When I scan the second part, the first part number gets moved to Dataset[2,0] and so on getting pushed down in the dataset table.
In the following, last 3 digits '492' was the first scan and it started at [1,0] and is now at [3,0]
This is already an existing system, and I am also new to Ignition. Also, they have other functions on the screen like deleting, updating position etc where they needed direct DB access.
For now, I am trying to optimize without rewriting much as more perspective sessions like this are being added causing more congestions.
I did exactly the same yesterday. Returning stack pos with serial from the database into dataset and used lookup function to populate serial based on stack pos.