Query Time-out error

[Clearscada_EIT]

I'm trying to run a query on the event Journal table, the query runs fine and it shows the result in the list, but it take little long to get the data to show up in the list because it's looking at 6 Months of date from the table . However, when I try to Server.query property in my script,

it splits out this error and returns ZERO row ( screenshot attached). Looks like it's caused by timeout error

Please help , thanks

[sbeadle]

The web request can time out before the query execution time is reached. While there may be a way to extend this, I would recommend looking at the query and optimising it, perhaps shorten the duration over which is searches, or simplify/remove wildcarding which causes additional searching. There's a time that users are prepared to wait for data, which is why web requests time out is not a bad thing.

[Clearscada_EIT]

Thanks for your response 

My client is fully aware of the system might potentially overload if the query takes too long to execute. However, they still insists on 6 MON time interval. What's the best way to extend the execution time?

thanks

[geoffpatton]

Maybe this should be moved to something outside of ClearSCADA. Setup a SQL server to store this information it in its own database. Get the relevant existing Journal data in in small enough chunks to prevent degrading Geo SCADA's performance and then have procedures in the SQL server poll the Journal at small intervals daily for the desired data. Then the customer can view the 6 Months of data from the SQL server and not put any adverse load on the Geo SCADA database. I have seen something similar done from Microsoft SQL. The free version holds a pretty large amount of data so that might work for you. Regardless of what SQL server used you might need to implement a procedure to remove old records to manage the database size.

[sbeadle]

The timeout is client-side, so I don't believe there are settings in Geo SCADA to increase it.

Geoff's suggestion is one way to handle this.

You could check if your query uses wildcard searches - they are usually less efficient, e.g. if you are looking for messages which match source FullName strings. Your client could get database IDs of columns first, then filter on those (Id = 23214 or ID = 432432 or ID = 432432) may be faster than (FullName like 'ABC%.%.D')