Help Center/ GaussDB(DWS)/ Developer Guide/ Standard data warehouse (9.1.0.x)/ GaussDB(DWS) Development Design Proposal/ GaussDB(DWS) SQL Statement Development Specifications/ INSERT Operation
Updated on 2024-12-18 GMT+08:00
View PDF
Share

INSERT Operation

Rule 3.3: Replacing INSERT with COPY for Efficient Multi-Value Batch Insertion

Impact of rule violation:

  • Parsing multiple values is time-consuming and resource-intensive, leading to low efficiency when importing data into the database.

Solution:

  • Instead of using INSERT VALUES, the frontend should use APIs like CopyManager of JDBC.

Suggestion 3.4: Avoiding Performing Real-time INSERT Operations on Common Column-store Tables

Impact of rule violation:

  • Importing a small batch of data in real-time to a common column-store table can significantly expand the small CU, occupying a lot of storage space and impacting the query performance.

Solution:

  • In real-time INSERT scenarios, evaluate the amount of data to be imported at once and the total amount of data. If the total amount of data is small, use row-store tables.
  • In the real-time INSERT scenario, import around 60,000 data records to a single table, partition, or DN at a time. The minimum import batch is 5,000 data records.
  • In the real-time INSERT scenario, use H-Store column-store tables (for version 8.3.0 or later).