Bulk INSERT vs Single INSERT Performance

intermediate

E-commerce site needs to import 10,000 products from supplier CSV. Traditional approach: Loop with single INSERT per product. Problem: Very slow (10 seconds for 10K products). Solution: Use bulk INSERT with multiple VALUES tuples (0.1 seconds, 100x faster).

-- Problem: Single INSERT in loop (very slow)

-- Application code (pseudocode):

-- for each product in csv:

-- INSERT INTO products (name, price, category) VALUES (product.name, product.price, product.category);

-- Time: 10 seconds for 10,000 products

-- Why slow: 10,000 transactions, 10,000 network round-trips, 10,000 index updates

-- Solution 1: Bulk INSERT with multiple VALUES (100x faster)

INSERT INTO products (name, price, category, stock)

VALUES

('Laptop Pro 15"', 1299.99, 'Electronics', 50),

('Wireless Mouse', 29.99, 'Electronics', 200),

('Office Desk', 399.99, 'Furniture', 30),

('Ergonomic Chair', 249.99, 'Furniture', 45),

('USB-C Cable', 12.99, 'Electronics', 500),

('Monitor 27"', 449.99, 'Electronics', 75),

('Desk Lamp', 39.99, 'Furniture', 120),

('Keyboard Mechanical', 89.99, 'Electronics', 90),

('Notebook Set', 15.99, 'Office Supplies', 300),

('Pen Pack', 8.99, 'Office Supplies', 400);

-- ... continue for all 10,000 products

-- Time: 0.1 seconds for 10,000 products (100x faster)

-- Why fast: 1 transaction, 1 network round-trip, 1 index update

-- Solution 2: Batch inserts (balance performance and memory)

-- Insert 1000 rows at a time

BEGIN;

INSERT INTO products (name, price, category, stock) VALUES

-- 1000 rows here

('Product1', 10.00, 'Category1', 100),

('Product2', 20.00, 'Category2', 200);

-- ... 998 more rows

COMMIT;

BEGIN;

INSERT INTO products (name, price, category, stock) VALUES

-- next 1000 rows

('Product1001', 10.00, 'Category1', 100);

-- ... 999 more rows

COMMIT;