On Thu, Sep 19, 2024 at 11:31 AM Ron Johnson <ronljohnso...@gmail.com> wrote:
> > [snip] > >> >> Method-4 >> >> INSERT INTO parent_table VALUES (1, 'a'), (2, 'a'); >> INSERT INTO child_table VALUES (1,1, 'a'), (1,2, 'a'); >> commit; >> > > If I knew that I had to load a structured input data file (even if it had > parent and child records), this is how I'd do it (but probably first try > and see if "in-memory COPY INTO" is such a thing). > > I was trying to reproduce this behaviour using row by row commit vs just batch commit vs true batch insert as you mentioned, i am not able to see any difference between "batch commit" and "true batch insert" response. Am I missing anything? CREATE TABLE debug_log ( method1 TEXT, start_time TIMESTAMP, end_time TIMESTAMP, elapsed_time INTERVAL ); CREATE TABLE parent_table ( id SERIAL PRIMARY KEY, name TEXT ); CREATE TABLE parent_table2 ( id SERIAL PRIMARY KEY, name TEXT ); CREATE TABLE parent_table3 ( id SERIAL PRIMARY KEY, name TEXT ); DO $$ DECLARE num_inserts INTEGER := 100000; batch_size INTEGER := 50; start_time TIMESTAMP; end_time TIMESTAMP; elapsed_time INTERVAL; i INTEGER; BEGIN -- Method 1: Individual Inserts with Commit after every Row start_time := clock_timestamp(); FOR i IN 1..num_inserts LOOP INSERT INTO parent_table VALUES (i, 'a'); COMMIT; END LOOP; end_time := clock_timestamp(); elapsed_time := end_time - start_time; INSERT INTO debug_log (method1, start_time, end_time, elapsed_time) VALUES ('Method 1: Individual Inserts with Commit after every Row', start_time, end_time, elapsed_time); -- Method 2: Individual Inserts with Commit after 100 Rows start_time := clock_timestamp(); FOR i IN 1..num_inserts LOOP INSERT INTO parent_table2 VALUES (i, 'a'); -- Commit after every 100 rows IF i % batch_size = 0 THEN COMMIT; END IF; END LOOP; -- Final commit if not already committed commit; end_time := clock_timestamp(); elapsed_time := end_time - start_time; INSERT INTO debug_log (method1, start_time, end_time, elapsed_time) VALUES ('Method 2: Individual Inserts with Commit after 100 Rows', start_time, end_time, elapsed_time); -- Method 3: Batch Inserts with Commit after all start_time := clock_timestamp(); FOR i IN 1..(num_inserts / batch_size) LOOP INSERT INTO parent_table3 VALUES (1 + (i - 1) * batch_size, 'a'), (2 + (i - 1) * batch_size, 'a'), (3 + (i - 1) * batch_size, 'a'), (4 + (i - 1) * batch_size, 'a'), (5 + (i - 1) * batch_size, 'a'), (6 + (i - 1) * batch_size, 'a'), (7 + (i - 1) * batch_size, 'a'), (8 + (i - 1) * batch_size, 'a'), (9 + (i - 1) * batch_size, 'a'), (10 + (i - 1) * batch_size, 'a'), (11 + (i - 1) * batch_size, 'a'), (12 + (i - 1) * batch_size, 'a'), (13 + (i - 1) * batch_size, 'a'), (14 + (i - 1) * batch_size, 'a'), (15 + (i - 1) * batch_size, 'a'), (16 + (i - 1) * batch_size, 'a'), (17 + (i - 1) * batch_size, 'a'), (18 + (i - 1) * batch_size, 'a'), (19 + (i - 1) * batch_size, 'a'), (20 + (i - 1) * batch_size, 'a'), (21 + (i - 1) * batch_size, 'a'), (22 + (i - 1) * batch_size, 'a'), (23 + (i - 1) * batch_size, 'a'), (24 + (i - 1) * batch_size, 'a'), (25 + (i - 1) * batch_size, 'a'), (26 + (i - 1) * batch_size, 'a'), (27 + (i - 1) * batch_size, 'a'), (28 + (i - 1) * batch_size, 'a'), (29 + (i - 1) * batch_size, 'a'), (30 + (i - 1) * batch_size, 'a'), (31 + (i - 1) * batch_size, 'a'), (32 + (i - 1) * batch_size, 'a'), (33 + (i - 1) * batch_size, 'a'), (34 + (i - 1) * batch_size, 'a'), (35 + (i - 1) * batch_size, 'a'), (36 + (i - 1) * batch_size, 'a'), (37 + (i - 1) * batch_size, 'a'), (38 + (i - 1) * batch_size, 'a'), (39 + (i - 1) * batch_size, 'a'), (40 + (i - 1) * batch_size, 'a'), (41 + (i - 1) * batch_size, 'a'), (42 + (i - 1) * batch_size, 'a'), (43 + (i - 1) * batch_size, 'a'), (44 + (i - 1) * batch_size, 'a'), (45 + (i - 1) * batch_size, 'a'), (46 + (i - 1) * batch_size, 'a'), (47 + (i - 1) * batch_size, 'a'), (48 + (i - 1) * batch_size, 'a'), (49 + (i - 1) * batch_size, 'a'), (50 + (i - 1) * batch_size, 'a')); COMMIT; END LOOP; COMMIT; -- Final commit for all end_time := clock_timestamp(); elapsed_time := end_time - start_time; INSERT INTO debug_log (method1, start_time, end_time, elapsed_time) VALUES ('Method 3: Batch Inserts with Commit after All', start_time, end_time, elapsed_time); END $$;