Tuning Options

Tuning options that provide faster query execution.

• Optimizing for Typical Usage
  Set the USER_RESOURCE_RESERVATION option to adjust memory use for the number of current users.
• Optimizing for Large Numbers of Users
  Adjust the startup parameters for large numbers of users.
• Restricting Concurrent Queries
  Set the -iqgovern switch to specify the number of concurrent queries on a particular server. This is not the same as the number of connections, which is controlled by your license.
• Limiting Query Temp Space
  Set the QUERY_TEMP_SPACE_LIMIT to specify the maximum estimated amount of temp space before a query is rejected.
• Limiting Queries by Rows Returned
  Set the value of the QUERY_ROWS_RETURNED_LIMIT option to prevent the optimizer from rejecting queries with large result sets.
• Forcing Cursors to be Non-Scrolling
  Eliminate the temporary store node in queries that return a very large result set to improve performance.
• Limiting the Number of Cursors
  Set the MAX_CURSOR_COUNT option to prevent a single connection from taking too much available memory or CPU resources.
• Limiting the Number of Statements
  Set the MAX_STATEMENT_COUNT option to limit the number of prepared statements for a connection can make.
• Prefetching Cache Pages
  Set the BT_PREFETCH_MAX_MISS option to control prefetch memory behavior.
• Controlling the Number of Prefetched Rows
  Set the PrefetchRows and PrefetchBuffer parameters to improve performance on cursors under certain conditions. This is a client option that you can set on the ODBC connection dialog, or in the .odbc.ini file.
• Controlling File System Buffering
  On some file systems, you can turn file system buffering on or off. Turning file system buffering off usually reduces paging and improves performance.
• Optimizing the Cache Partitions
  Changing the CACHE_PARTITIONS value may improve load or query performance in a multi-CPU configuration.