Sybase 12.4.2 Tuning bulk loading of data, Improving load performance during database definition

CHAPTER 5 Moving Data In and Out of Databases

For information on setting DBISQL database options, see “SET OPTION statement” in the Adaptive Server IQ Reference Manual.

Tuning bulk loading of data

Loading large volumes of data into a database can take a long time and use a lot of disk space. There are a few things you can do to save time.

Improving load performance during database definition

The way you define your database, tables, and indexes can have a dramatic impact on load performance.

Optimizing for the number of distinct values

Adaptive Server IQ optimizes loading of data for a large or small set of distinct values, based on parameters you specify when you create your database and tables. Parameters that affect load optimization include:

•The UNIQUE and IQ UNIQUE options, and the data type and width of the column, all specified in the CREATE TABLE or ALTER TABLE command.

•The IQ PAGE SIZE, specified in the CREATE DATABASE command.

For details of how these parameters affect loading, and information on how to specify them, see “Creating tables” and “Choosing an IQ page size”.

Creating indexes

To make the best use of system resources, create all of the indexes you need before loading data. While you can always add new indexes later, it is much faster to load all indexes at once.

221