Optimizing Bulk Inserts Into an Identity Column - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

The performance of bulk INSERT operations into a table with an identity column depends on the DBS Control setting for the IdCol batch size parameter (see Teradata Vantage™ - Database Utilities, B035-1102 for information about DBS Control). You should use this variable to optimize the trade off between bulk insert performance and your tolerance for the identity column numbering gaps that can occur with a system restart. The performance enhancements realized by larger batch size are due to the decrease or elimination of updates to DBC.IdCol that must be performed to reserve identity column numbers for a load.

While a larger IdCol batch size setting enhances bulk insert performance, it also means that more identity column numbers are lost if a system restart occurs. Reserved identity column numbers are memory-resident. If the database must be restarted, the batch insert transaction is rolled back, and those reserved identity column numbers are permanently lost.

The optimal batch size for your system is a function of the number of AMPs. The IdCol batch size default value of 100,000 rows should be sufficient for most workloads, but you should perform your own tests to determine what works best for your applications and system configuration.

When you are bulk inserting rows into an identity column table during a Teradata mode session, be careful not to upgrade the default lock severity of a SELECT operation on DBC.IdCol from ACCESS to READ or higher severity if the SELECT is performed within the boundaries of an explicit transaction because you run the risk of creating a deadlock on the IdCol table.