i have a table that have 1 line, this top1 line will refresh every second by another software
in SQL server side, if i execute select the update query it will response very quickly
But in the ignition side , i found the update is very slow, sometimes 1 minutes, sometimes 3 minutes.
my setting update rate is 1s
i check the scada server task manager, seems the sql server memory and Cpu is very high.
i don’t know what block the query update,here is the ignition server status
In the gateway if you navigate to Status > Databases > Connections > and then Details for the current database
Do you see any entries of long running queries and how long it is taking them to execute? I know its a small table, but if for some reason the queries are taking a while to respond to Ignition, you may see that they are bottle-necking. The “Other software” and Ignition may be fighting to get access to the table at the same time and Ignition losing the battle.
i have defined many tags in the tag tree, i have define them scaning speed is 240s
but i can’t understand why they are still query very quick if i observe at the screen Status>Databases>Connections
You're sure it's 240 seconds? The time unit for tag groups is in milliseconds (ms).
You should also try the query in the SQL Management Studio. There you can see how long it takes, and you can even check the execution plan and take a look at what indexes are used.
Yes, you should search what is calling that query.
I see you have a very high “plan count” too. This happens when you don’t use JDBC variable replacements (the ?-signs, or variables in names queries), but rather use text replacements (like expressions in the query).
However, you can force parametrisation on the SQL server with this command. It should make all your recurring queries a bit faster:
ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED
And for that query with just 6 executions, but a high CPU time, you’d best check the execution plan.