Sybase 12.4.2 manual Setting server startup options, Adjusting your environment at load time, 222

Tuning bulk loading of data

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.

222