How to run a project use less server SQL resource

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

here is my sql activity read picture


i found there have a very big read action, perhaps that is the reason that make all my other query wait for a long time


this table RPL_DATA1 currently have 600K lines
image
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.


image
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
image
my current query is like this
image
In my understanding you suggest me use the sql like the following picture, the yellow line is the difference.
image

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
image
My current table’s id column is an identity column (PRIMARY KEY)
if I insert one column this id will increase 1


I want to know if I put id in the where it will speed up?

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:

1 Like

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
image
this tag is for the purpose to sorting some data from 600K columns , and every 1 minute, 1 more column will be insert into
image
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?

thanks a lot, this video is very clear :grinning:

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.

1 Like

A primary key is an index. It is also a unique & non-null constraint.

Well sort of, anyway, what I was trying to impart was that adding an extra column is not the same as adding an index.

Is there ever a time it isn’t? From everything I’ve seen from every database, that’s true. SQLite is kind of unique and it’s the same.

A primary key isn’t an index itself, an index is created on it. This is done automatically in most, if not all databases.

I guess that was my point…

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.

This is not the case. Though ‘Key’ and ‘Index’ are often used interchangeably.

A key is a column or set of columns in a database that can be used to locate one or more rows in a table.

An index is an implementation feature of a database used to store, search, and find rows in a table using a key.

When a Primary Key is added to a table the Database Engine creates a Unique Index on the Primary Key columns to enforce the data uniqueness.

In other words you can create a Unique Index without a primary key but you can not create a primary key without a Unique Index.

1 Like

Ergo, a primary key is an index. Thanks for confirming my statement.