If you have SQL Queries that need to be executed on a regular basis and return result sets that exceed the SQL Query row and size limits, 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 ClearSCADA Server Configuration Tool (see Accessing the ClearSCADA Server Configuration Tool)
- Expand the SQL Query Configuration branch.
- Select the Parameters branch to display the 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 varies depending on whether the server is 32-bit or 64 bit:
- The Query Row Limit field default for 32-bit servers is 1000000.
- The Query Row Limit field default for 64-bit servers 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 for 32-bit servers is 50MB
- The Query Size Limit field default for 64-bit servers 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).
- Right-click on the system icon in the tree-structure, and select the Apply Changes option to apply the changes.