Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 243 / 536

CHAPTER 5 Moving Data In and Out of Databases
223
• 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.