ZULUGIS 8.0.0.7754
Timeout - maximum SQL query execution time
The SQL (Structured Query Language) queries are widely used among the users of the ZuluGIS geographic information system. Their advantages over simple database queries are evident. First of all, they are executed not against a specific database but against all the layers and databases associated with them at once, and, secondly, the query dialect has been expanded in accordance with the OGC extension for SQL, which allows you to execute queries not only by textual and numeric data but also by spatial objects on the map.
The query language itself is not so simple, especially when you have to work with spatial datasets that contain hundreds of thousands of objects and records. When working with medium and large layers, writing the most efficient SQL queries is a priority. It is not always possible to form an effective structure of a query against a database, especially on the first attempt. Inefficiently written queries can take minutes, and in some cases hours, to complete, and to stop their execution, you need to close the program. When you close the program, the queries against layers published on ZuluServer continue to run, taking up the available connections to the server.
The images show examples of queries that complete the same task against the layer, with a total number of objects on the map amounting to 12,978. When you use the SQL queries, the ID of the utility vault to which the consumer is connected is entered into the consumer database, but the second query is performed 100 times faster. The reason why the first query has taken so long is that the geometry operators are inadequately optimized. They are convenient, but it is better to replace the double comparison of the object geometry within the same query with a simple JOIN operator.
Starting with the ZuluGIS version 8.0.0.7754 (release date — 24.03.2021), a timeout (maximum time) for executing an SQL query has been introduced; the default timeout value — 60 seconds. It allows you to avoid hang-ups when executing "long-running" queries. When the timeout expires, a corresponding error message appears.
If the query for updating object data requires 100 seconds to be executed, while the timeout is 60 seconds – the data will not be updated and the 'timeout exceeded' error message will appear.
You can control the timeout directly in the query body by specifying the maximum time in seconds, for example:
SET TIMEOUT <number>;
SELECT <a long-running query>; >;
To set an "infinite" timeout, use the INFINITE argument:
SET TIMEOUT INFINITE;
SELECT <a long-running query>;