Tuning c-treeSQL Queries

For certain queries, those involving sorting or joins, the c-treeSQL Server creates temporary tables which exist in memory and/or on disk, depending on the amount of data in the temporary table. The c-treeSQL Server provides options to help optimize these queries with regard to physical memory space available on the server. Specifying these options can greatly enhance performance when dealing with large result sets generating extremely large temporary tables.

An internal memory storage system provides a mechanism for the c-treeSQL Server to store data in memory instead of on disk. By using this internal storage system for volatile data such as temporary tables and dynamic indexes, the c-treeSQL server improves the performance of many queries, such as joins. Depending on the amount of memory available on a system, certain queries may create temporary tables too large to be stored in memory. In these cases, the internal storage system swaps blocks of data to a disk file as necessary. The following variables allow implementations to control the characteristics of how this internal storage system uses memory to create temporary tables.

• SETENV TPE_MM_CACHESIZE: Specifies the size, in kilobytes, of the memory cache used for temporary tables. The default value is 1,000 Kb of memory. The internal storage system uses this cache for storing temporary tables when sorting and creating dynamic indexes during processing. Increasing TPE_MM_CACHESIZE improves performance by reducing the need to write to the on-disk swap file.
  
  The TPE_MM_CACHESIZE setting determines how much memory the SQL engine uses for its temporary tables for each SQL client. Increasing this setting increases the amount of temporary table data the c-treeSQL Server stores in memory instead of writing this data to the disk-based swap file. But note that this memory is allocated for each SQL client, so you should consider how many clients will connect to the c-treeSQL Server at any given time and make sure total cache memory doesn't exceed available physical memory on the system.


• SETENV TPE_MM_SWAPSIZE: Specifies the maximum size, in kilobytes, of the swap file the internal storage system uses when it writes to disk from the main memory cache. The default is 500,000 Kb. The sorting of data larger than this size results in the following c-treeSQL error
  
  (-16001):MM No data block
  
  To resolve this error, the TPE_MM_SWAPSIZE limit needs to be increased.


• Increasing the c-tree Server's DAT_MEMORY (data cache size) and IDX_MEMORY (index cache size) settings can significantly reduce the number of bytes read from disk while executing a query. In one case, when using the server's default cache settings, executing a query involved reading about 2.2 Gb of data from disk. Changing to use a 150 Mb data cache and a 100 Mb index cache reduced disk reads to about 120 Mb of data instead.