Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 242 / 536

Tuning bulk loading of data
222
Adding dbspaces
If you run out of space while loading data, Adaptive Server IQ prompts you to
create another dbspace, and then continues the operation after you add the
dbspace. To avoid this delay, make sure that you have enough room for all of
the data you are loading before you start the load operation. Use the
sp_estspace
or
sp_iqestdbspaces
stored procedure to help you estimate the
space you need for the database and its dbspaces.
To that ensure are you able to add a new dbspace if you do run out of space, see
the “RESERVED_TEMP_DBSPACE_MB” and
“RESERVED_MAIN_DBSPACE_MB” options in the Adaptive Server IQ
Administration and Performance Guide.
Setting server startup options
On some platforms you can set command-line options to adjust the amount of
memory available. Increasing memory can improve load performance. See
Chapter 2, “Running Adaptive Server IQ” for command-line options that
affect performance.
Adjusting your environment at load time
When you load data, you can adjust several factors to improve load
performance:
• Use the
LOAD TABLE
command whenever you have access to raw data in
ASCII or binary format. especially for all loads of over a hundred rows.
The
LOAD TABLE
command is the fastest insertion method.
• When loading from a flat file, use binary data if you have a choice of using
binary or character data. This can improve performance by eliminating
conversion costs and reducing I/O.
• Set
LOAD TABLE
command options appropriately, as described in “Bulk
loading data using the LOAD TABLE statement”. In particular, if you
have sufficient memory to do so, or if no other users are active during the
load, increase the
BLOCK FACTOR
.
• Place data files on a separate physical disk drive from the database file, to
avoid excessive disk head movement during the load.