When I set up SCADA signals with a 24-hour period, it works and I am able to view SCADA historical results in both the SCADA Signal preview menu or SCADA elements in the model. However, if I change to another time frame (such as 48 hours), WaterGEMS crashes suddenly and without any error messages.
I thought at first that it was due to the complexity of the model and the large number of signals, but the problem is reproducible with an empty model and only a single signal.
Version information: 10.01.01.04 32-bit
The following procedure works without issue:
However, if the exact same procedure is followed, except the second timestamp is changed to (TimeStamp<=08/22/2018), when you click Okay from entering the Custom SQL Statements, the program freezes for about 30 seconds, then crashes completely. There is no warning message or 'crash report' dialogue.
If you change the date range to 8/21/2018 - 8/22/2018, it works fine, but a date range of 8/21/2018 - 8/23/2018 crashes. So there doesn't seem to be a problem with a specific date, just with ranges greater than 24 hours.
Aha - closing in on an answer! It looks like what WaterGEMS is doing is trying to verify the HistoricalSelectStatement as soon as the Custom SQL dialogue is closed, and the way it is verifying it is by dropping off the @requestedsignals filter. This means it is getting results for *all* Tagnames simultaneously. When the date range is 24 hours, this verifies correctly. But when the date range is larger, there are too many results, and Historian returns an exception that 'Execution stopped because a resource limit was reached.' WaterGEMS isn't prepared for that, and so it crashes.
That means the 24/48 hour limit is specific to my particular Historian database (that happens to be the tipping point with the current number of tags and values in our database), but the general problem is that WaterGEMS is trying to verify a query while stripping it of important filters, which risks getting too many results back.
I've verified this using a Python connection to Historian, using the query in the logs that is causing an error. If I execute the query ' set rowcount=0, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where ((TimeStamp>=08/20/2018) and (TimeStamp<=08/21/2018)) and Quality = "Good NonSpecific"' with the 24 hour range, I get results, if I execute it with a 48 hour window, I get an error that a resource limit was reached.
Hi Stephen, thanks for all the observations. We will discuss internally and get back to you shortly.
Regards,
Jesse DringoliTechnical Support Manager, OpenFlowsBentley Communities Site AdministratorBentley Systems, Inc.
One additional thought:
I had been noticing that when I had SCADA set up for a 24 hour query, after every model run the first SCADA Element I would try to Quick Graph would lag for a while before showing the graph, and then as I clicked around to other Elements they would graph very quickly. This seemed strange to me, as it should be the same amount of data each time. I figured it was just some initial connection lag.
But now it would make sense that perhaps on that initial connection to SCADA after running a simulation, WaterGEMS is first trying to verify the query generically, and doing the same stripping of the Tagname filter, causing the large number of returned records and the lag. So it may be that there are multiple places in the code where this verification is performed. If that's the case, not only would fixing it make things more stable, it would also improve performance by getting rid of that initial connection lag. I don't know if it's the same function being called each time (so fixing it once would fix it for all), or whether a similar routine is copied in multiple locations in the code.
Thanks, Stephen, our developer is currently looking into this. We'll keep you updated.
Hi Stephen,
Sorry for the delay on getting back to you on this.
Is there a specific reason to include hard coded date ranges into that custom query? If not, you should be able to leverage the @startdatetime and @enddatetime variables and when querying the custom query for column metadata SCADAConnect will substitute those variables such that the time range is tiny, and you should not run into any such issue.
Wayne.
ps/ More information on those date/time variables can be found here:
https://communities.bentley.com/products/hydraulics___hydrology/w/hydraulics_and_hydrology__wiki/33335/how-to-define-real-time-custom-queries-in-scadaconnect