I have several power tables that have extensions functions to handle cell edit-update SQL table, header formatting, cell formatting and pop triggers for copy/cut/paste functions. The power tables refresh quickly over ethernet and network wifi; however, over VPN connection the power tables start to lag even with a good internet connection available. All the data is being stored into SQL server on an on-premise server. I am using Ignition 7.9.4.
I ran the same queries in SQL server management studio over ethernet connection and vpn connection, but noticed minimal difference in duration (using the xEvent Profiler) ~6000 microseconds vs 7000-8000 microseconds. For this reason, I believe it’s the refresh/rendering of the power table causing the laggy performance after the SQL table has already been updated. Any thoughts on speeding up the refresh/rendering? Any help would be greatly appreciated.
Didn’t get an email notification on this reply. Polling is off on the power table datasource. However, I am using system.db.refresh on the OnCellEdited extension function of the power table. This allows the database update whenever a cell value is changed without need for a button.
Below are the query times for a Select query with 12 joins resulting in ~3000 records according to SQL Server.
I ran the query (3) times for each connection:
Network wired connection:
CPU time = 889 ms, 858 ms, 889 ms
elapsed time = 948 ms, 938 ms, 940 ms
Network wireless connection:
CPU time = 952 ms, 858 ms, 936 ms
elapsed time = 1005 ms, 960 ms, 957 ms
VPN Connection (internet connection off of mobile phone hotspot at same physical location as network site):
CPU time = 655 ms, 920 ms, 936 ms
elapsed time = 1132 ms, 925 ms, 1011 ms
It seems to me like the VPN connection is just slow to transfer all the data.
The query executes in the same time in your tests because queries always execute on the gateway. But then the results have to be streamed back to the client, which is likely just much slower over a VPN connection.
I’m not sure if there’s any logging that would help you measure this but you might be able to use Wireshark to see the HTTP traffic between your client and the gateway.
What would be some alternatives to have a fast response time on the client side but keeping the data centrally stored on our on-prem server? Basically, dealing with an application in which client is performing manual data entry of in-process measurements on a production line at a remote customer site (either using customer wifi or mobile hotspot) so response time is critical. I thought of database replication but that could get messy. What about writing to a csv file locally, then import to sql server when there is a good connection?
We will also be moving our server onto the cloud this year, will this have a positive impact on the response time?