Tuning c-treeSQL Queries

For queries involving sorting and/or joins, c-treeACE SQL may create temporary tables which exist in memory and/or on disk, depending on the amount of data in the temporary table. c-treeACE SQL provides options to optimize these queries with regard to physical memory space available on the server. Tuning 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 c-treeACE SQL 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-treeACE SQL 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 (1MB) 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-treeACE SQL stores in memory instead of writing this data to the disk-based swap file. However, note that this memory is allocated for each SQL client, so you should consider how many clients will connect to c-treeACE SQL 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 (500 MB). The sorting of data larger than this size results in the following c-treeACE SQL error

  (-16001):MM No data block

  To resolve this error, the TPE_MM_SWAPSIZE limit needs to be increased. (Maximum value: 2097151 for 2GB of swap file)

  Note: These swap files can be found during an active connection with a filename beginning with ‘M’ followed by a string of random alphanumerics. These files can sometimes be left around after an abnormal server shutdown. They can be safely deleted in such a case.

• 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.