When you load data, you can adjust several factors to improve
load performance.
- Use the LOAD TABLE command if you have access
to raw data in ASCII or binary format, especially loads of
more than one 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 between
binary or character data. This can improve performance by
eliminating conversion costs and reducing I/O.
- Set LOAD TABLE command options appropriately.
Set the LOAD TABLE
IGNORE CONSTRAINT option
limit to a non zero value if you are logging the ignored
integrity constraint violations. Logging an excessive number
of violations affects the performance of the load.
- Place data files on a separate physical disk drive from the database file, to
avoid excessive disk head movement during the load.
- Change the startup parameters to increase large memory and cache size. Providing
enough memory for the load is a key performance factor. On a
simplex server, large memory requirements are one third of
total available memory. To ensure adequate memory for the
main and temporary IQ stores, set the –iqlm, –iqtc, and –iqmc startup parameters
so that each parameter receives one third of all available
memory.
On multiplex servers,
large memory requirements are determined by the node
or nodes that handle load operations. Increase the
large memory option on the coordinator or writer
node to an appropriate level for the load. Reader
nodes require significantly less memory for query
operations.
- Adjust the degree of buffer partitioning for your database or server, to avoid
lock contention. By default, buffer partitioning based on
the number of CPUs is enabled, and can be adjusted by
setting the -iqpartition
server command line option or the Cache_Partitions database option.
- 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. These resources expedite
your inserts.
- If you are using the INSERT statement, run
Interactive SQL 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.
If you
are using INSERT...LOCATION to load large amounts
of text or bulk data across a network from a remote
Adaptive Server Enterprise database, use the
PACKETSIZE parameter of the LOCATION clause to increase the TDS
packet size. This change may significantly improve
load performance.