Hi All,
Im using Ignition 8.1.35 with Vision.
We have made a small search tool to search for events in a special table in SQL Server that gets data from the alarm journal.
We have had some help making the right indexes and searching is generally fast.
From the GUI we call runPrepQuery using the example from the documentation system.util.invokeAsynchronous:
def longProcess(rootContainer = event.source.parent):
import system
# Do something here with the database that takes a long time
results = ...( call runPrepQuery )
# Now we'll send our results back to the UI
def sendBack(results = results, rootContainer = rootContainer):
rootContainer.resultsProperty = results
system.util.invokeLater(sendBack)
system.util.invokeAsynchronous(longProcess)
I have seen some examples of users making queries that run longer than the 1 minute timeout on the Gateway
and the query ends up hanging in the Gateway and I need to cancel the query in the Gateway.
Can you somehow cancel the query before it times out in the Gateway?
I've looked at the transaction parameter (tx) for the runPrepQuery, but that put's a lock on my table and that is it not the way to go.
To my knowledge there is no mechanism for canceling a query from Ignition. The best you can probably do is to catch the timeout exception and then react appropriately.
I'm just starting to realise that the SQL query run in a Gateway scope, and the timeout that the user sees is between the SCADA client
and the Gateway (client scope).
Interestining idea from Benjamin, I'm thinking would it be possible to make a monitor thread, that gets the
time from system.util.getReadTimeout() and a few seconds before the limit is reached it kills the database query thread if there are no result from the database?
That still won't kill the query running in the database. The only way to run really long queries in a Vision client is to start the query in an async thread or thread pool via a gateway message handler, track it with some persistent dictionary, and send the final result back with system.util.sendMessage() from the gateway to target the specific client ID. The initial request would use system.util.sendRequest() and would return a token to use to identify the proper result in the client message handler.
It is doable, but a complicated mess. It is better to redesign your DB so your queries are near-instant. Use precomputed tables and/or materialized views to limit the query work at time of use.
Thanks Phil,
I agree that would be a messy implementation, that my colleagues and me would have to maintain
The table has almost 75 mio. rows I'm not sure that I can make every query that a user can think of run fast.
Another option could be to handle the sql query timeout in a python fastapi webservice, that sends a kill command in sql server
if it takes longer than 1 minute. Ignition could use the system.net.httpget to send queries, but then again I have some external
code that needs to be maintained, and keept up to date.
I think my best option is to set some limits in the userinterface, so that a user cannot query a years data without using an indexed column.
I feel like we have this conversation all over this forum about what users can handle and consume. I do data analytics everyday of my life at work, and 75 million rows is beyond any number I could consume and use. Your users will also not really benefit from that much data all at once.
I agree you should add some limitations. You as the developer in conjunction with your end user need to identify your User Requirements. If the most data they need at once is say 3 months, give them the option for 3-6 months or something of that sort.
If you have a table with millions of rows, make sure its indexed properly, and you may implement a read table and a write table. If you are constantly writing to a huge table and also reading from it that can cause long running queries as well.
Sorry for the delayed answer.
The tabel is partitioned in monthly batches and we are using both Clustered Columnstore indexes and Non-Clustered indexes.
Most searches are on events that happend within the last 24 hours and a query takes about 1-2 seconds and monthly maybe 2-5 seconds.
The problem is when a user queries several months and with a column that has not been indexed, then it would be nice to say to the user 'Hey
your query is taking longer than usual so it is stopped', but that is not an option. My solution is to handle it in the user interface
if the user makes a search for several months in a column that is not indexed I display a warning "Are you sure..." I think that is the
best I can do right now. Maybe there will be some better database handling in Ignition in the future
I agree that there is a limit to how much data a user can consume. However, if our technicians can diagnose error events themselves, it becomes a powerful tool. By answering questions such as ‘When did this event last occur?’, ‘How often does it usually happen?’, and ‘How did we handle it the last time?’, they start to truly understand the equipment they work with. It’s more than just a gut feeling.
Perhaps you should not let them do that. Most databases will let you query table/column/index metadata so you can pre-determine what columns should be allowed in your WHERE clause.
I think 'system.db.rollbackTransaction' is something that could help with the problem you're having, but I would consider it as a temporary patch because your database will continue to grow over time, a better solution would be to optimize your database and the way you're handling that amount of data, maybe generate those reports internally in SQL and not through Ignition, this way Ignition only displays them but doesn't perform the entire transaction load, from my experience queries executed from Ignition that last more than 30 seconds require attention and deep analysis since they can cause frozen screen, memory leaks and loss of control of DB transaction logs.
I’ve done some further performance testing of problematic queries, and the SQL performance is very good. Most queries finish in under a minute, so it really bothers me to limit the options that the user has.
I just need a safety net to filter the queries that take too long. So, I started to look into the solution Phil mentioned about starting the query in a message handler.
I’ve set up a little test with a button that sends a request to a message handler to simulate a query to the database.
In the GW message handler, I start a Query Monitor in its own thread that does two things. If the query finishes within some timeout limit, a success message is sent to a client-side message handler that just shows a popup message. If the query takes too long and times out, the thread is stopped, and an interrupt message is sent to the user.
I’m nowhere near finished. I still need to make another thread that can start the query in a thread so that I can keep monitoring from the Query Monitor. I also need to make some code that can stop the query in the database and kill the session.
This is my Query Monitor so far:
import threading
import time
class QueryMonitor(threading.Thread):
TIMEOUT = 5 # Allowed timeout in seconds
QUERY_RUNTIME = 10 # Query running length (for testing)
STEP_INTERVAL = 1 # step in seconds
# Constructor init variables
def __init__(self):
super(QueryMonitor, self).__init__()
self.is_query_complete = False
self.result = ""
self._stop_event = threading.Event()
self._complete_event = threading.Event()
def stop(self):
self._stop_event.set()
# Overridden from threading.Thread
def run(self):
logger = system.util.getLogger("QueryMonitor")
i=0
try:
# Simulate a long-running query
for _ in range(QueryMonitor.QUERY_RUNTIME):
if self._stop_event.is_set():
# Do async database query here
# ...
self.result = "Long running query interrupted!"
self._complete_event.set()
return
# Intervals to check if we have got a result
time.sleep(QueryMonitor.STEP_INTERVAL)
self.result = "Query completed with success"
self.is_query_complete = True
except Exception as e:
print("Query was interrupted")`Preformatted text`
