BackGround: I have ignition as a tool to develop the operator interface to collect realtime data from production line. With more and more app develped, sql resource become a big issue
the following picture is my sql activity’s execution picture
this table RPL_DATA1 currently have 600K lines
these tag is Event Driven, almost have 100 these kind of tags in the Gateway.
(because I have 9 machine, I firstly prepare them on the gateway tag, then use indirectly binding to show them on the vision)
I don’t know why these 100 event driven tag will make the ignition tag’s sql function almost go wrong,
What should I do to improve the software structure and make ignition more stable ?
It is good to separate operational data from the historical data used for reporting. You usually only need short term operational data to run a production line. So, either separate into different tables in the database (a small fast table with short term operational data, and a large slower table with historical data), or keep the operational data in ignition tags. Ether way, it will speed up data retrieval for displaying to operators running the line. Then have a different display for reviewing historical data that is not as time critical.
If you haven’t already, you could also add indexes to the slow tables to speed up queries. Properly formed indexes can cut retrieval times dramatically. I have similar tables with over 60M records and adding the right indexes made a huge difference.
First, make sure your table is indexed properly.
Secondly, look at creating a single query to bring back all values, then bind into that dataset tag using derived or expressions tags. This will minimize the requests to your database.
You can see my tag, all of them the difference is the yellow highlighted difference, that is use to sorting different work time
How to query to bring back all values in 1 query use the sql skill? Thanks a lot
I have two database as your suggestion, about use the add the right indexes suggestion , I have checked my table, I have a formed indexes, you can see the picture my current max id is 744498
my current query is like this
In my understanding you suggest me use the sql like the following picture, the yellow line is the difference.
If my understanding is correct , always production keep every minutes, the max id will increase every minute, currently job always have to query the data from the last 2000 columns, this id is manually input by my self , how to keep the id update automatically every week or every month? Thanks a lot.
Hi @ yifeiliu08
To speed up the queries you need to add indexes in the database itself. This is slightly different depending on the type of database you are using. This is not adding an extra column to your table. What database are you using? https://www.tutorialspoint.com/sql/sql-indexes.htm
Hi DavidWisely
my database is Microsoft SQL
My current table’s id column is an identity column (PRIMARY KEY)
if I insert one column this id will increase 1
If there is a column or collection of columns that can be used as a unique index, consider using it (them) as the primary key instead. And ditch the id column. Your shift start timestamp is a likely candidate.
I think you are confusing your primary key with an index. (even though there is probably an index on your primary key).
You could view this video to understand indexes a bit better:
Dear pturmel, thanks for your suggestion, I am not very clear about the sql update rule in ignition, here is my current status
for tag side , I create the query tag
this tag is for the purpose to sorting some data from 600K columns , and every 1 minute, 1 more column will be insert into
current I choose the execution mode is Event Driven, (200 tags seems like this)
I found it seems it refresh all the time , does that means in this 600k lines table if it add 1 column , that will trigger the event driven mode, make the query active?
If yes, I should change the execution mode to tag group (600s) it will reduce the update speed?
In event driven mode, your query will run whenever the tags referenced in the query change. If you want it to run once every ten minutes, then yes, use tag group execution.
It isn’t most. It is all. Indices are ‘keys’. The primary key is an index. As primary, it must also uniquely identify every row (a constraint). Some databases’ management tools show the primary key under a table’s list of constraints. Others show it under the table’s list of indices. MS SQL Server does the latter, fwiw.
Early versions of MySQL didn’t support multi-column index behavior for primary keys, and Ignition tries to accommodate that by defaulting to a serial number primary key in transaction groups. Over-exposure to lame old MySQL may explain David’s misunderstanding.
