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.