The Parameters section of the Server Configuration Tool includes properties that you can use to define the maximum number of rows and the maximum amount of memory permitted in a single SQL Query result set. You might have to adjust these settings if you have SQL Queries that have to be run on a regular basis and return large result sets that exceed the default limits. If this happens, you have two options:
- Use several SQL Queries that return smaller result sets. This is an effective solution but may be unsuitable for your requirements.
- Change the SQL Query Row Limit and Size Limit. By changing the limits, you allow SQL Queries to return result sets that contain a larger number of rows and take up more memory. However, while increasing the limits may allow you to return larger SQL Queries, it may also have a negative impact on system performance. For this reason, you should only change the row and size limits if you are certain that you cannot achieve satisfactory results by using multiple SQL Queries.
If you need to change the SQL Query Row and Size limits, you should:
- Access the Geo SCADA Expert Server Configuration Tool.
- Expand the SQL Query Configuration branch.
- Select the Parameters branch to display the SQL Query Parameters section.
- Use the Query Row Limit field to define the maximum number of rows that can be returned in the result set for a single SQL Query. The default amount is 5000000.
If you increase the Query Row Limit, you should also increase the Query Size Limit by a corresponding amount. You should maintain to maintain the ratio of these settings, for example, if you increase the Query Row Limit by 300% to 15000000 , you should also increase the Query Size Limit by 300% to 750MB.
ATTENTION: Increasing the Query Row Limit may affect performance.
- Use the Query Size Limit field to define the maximum size, in megabytes, that can be used to store the result set of a single SQL Query. The Query Size Limit field default is 250MB.
- Usually, you will only need to increase the Query Size Limit if you have also increased the Query Row Limit (the Query Size Limit needs to be increased by the same percentage amount as the Query Row Limit, as mentioned in step 4).
- Apply the changes to the server.
- Perform the same procedure on any other servers on your system.