Sybase 12.4.2 manual Reducing Main IQ Store space use in incremental loads, 223

CHAPTER 5 Moving Data In and Out of Databases

•Increase the size of the database cache. Providing enough memory for the

load is a key performance factor. Use the SET OPTION command to adjust MAIN_CACHE_MEMORY_MB and TEMP_CACHE_MEMORY_MB. For these options to take effect, you must ensure that no users are using the database where you set the option, and then disconnect from the database. You can then reconnect and allow other users to connect.

•Adjust the amount of heap memory used by load operations by using the SET OPTION command to change the LOAD_MEMORY_MB option. When LOAD_MEMORY_MB is set to the default (0), Adaptive Server IQ uses the amount of heap memory that gives the best performance. If your system runs out of virtual memory, specify a value less than 500 and decrease the value until the load works. For insertions into wide tables, you may need to set LOAD_MEMORY_MB to a low value (100-200 MB). If you set the value too low, it may be physically impossible to load the data.

•Ensure that only one user at a time updates the database. While users can insert data into different tables at the same time, concurrent updates can slow performance.

•Schedule major updates for low usage times. Although many users can query a table while it is being updated, query users require CPU cycles, disk space, and memory. You will want these resources available to make your inserts go faster.

•If you are using the INSERT statement, run DBISQL or the client application on the same machine as the server if possible. Loading data over the network adds extra communication overhead. This might mean loading new data during off hours.

Reducing Main IQ Store space use in incremental loads

An incremental load may modify a large number of pages within the table being loaded. As a result, the pages are temporarily versioned within the main dbspace, until the transaction commits and a checkpoint can release the old versions. This versioning can be particularly prevalent if the incremental load follows a delete from the same table. The reason for this is that, by default, Adaptive Server IQ (by default) reuses row IDs from deleted records.

Setting this option to OFF reuses ROWIDs from deleted rows. To help reduce space usage from versioned pages, set the APPEND_LOAD option ON so that IQ appends new data to the end of the table. APPEND_LOAD is OFF by default.

223