How do you load or change large amounts of data efficiently (bulk insert and batching)?
How do you load or change large amounts of data efficiently (bulk insert and batching)?
Row-by-row statements are slow because each one pays for a network round trip, parsing, and often a commit. Efficient loading reduces round trips and commits and uses the engine's bulk path.
BEGIN;
INSERT INTO events (user_id, type) VALUES (1,'a'), (2,'b'), (3,'c'); -- hundreds to thousands of rows per statement
COMMIT;
Batch sizes in the hundreds to a few thousand rows are typical; measure on your system.
| Engine | Tool |
|---|---|
| PostgreSQL | COPY events FROM '/path/file.csv' WITH (FORMAT csv, HEADER) or \copy from the client |
| MySQL | LOAD DATA INFILE (LOCAL is disabled by default for safety) |
| SQL Server | BULK INSERT, bcp, or SqlBulkCopy from .NET |
| Oracle | SQL*Loader, external tables, INSERT /*+ APPEND */ ... SELECT (direct path) |
JDBC (pgjdbc) connection property: reWriteBatchedInserts=true
MySQL Connector/J connection property: rewriteBatchedStatements=true
Use the driver's batch API (addBatch / executeBatch) instead of a loop of single executes.
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.