Non-linear insert times in table with FK

[IanP]

Hi,

I need to do batch inserts into a table with a foreign key reference. All the batches need to run in the same transaction. To execute each batch I use a prepared statement, set values on it and execute it multiple times. What I find is that the cumulative insert time for each batch of inserts goes up quite steeply until I commit. Without the fk ref the inserts seem fairly linear. My questions are:

Is the below a bug or expected behaviour?
Is there some alternative structure I could use that would be likely to avoid this and make the insert times linear again, for example building insert statements with multiple values clauses instead of using a prepared statement?

All of the test cases below are based on slight alterations to this code...

drop table if exists fk1_table;
create table fk1_table ( id bigint primary key, ref_col bigint);
insert into fk1_table values(1, 1);

drop table if exists test;
create table test (    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    col1 CHAR(1) DEFAULT 'N',
    col2fk1 BIGINT,
    col3 BIGINT,
    col4 BIGINT,
    col5 CHAR(1),
    col6 BIGINT,
    col7 BIGINT,
    col8 BIGINT,
    col9 BIGINT,
    col10 BIGINT,
    col11 VARCHAR(1000)
);
ALTER TABLE test ADD FOREIGN KEY(col2fk1) REFERENCES fk1_table(ID);

set autocommit off;

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );
@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );
COMMIT;

The structure of the test table is exactly the same as the table in my application. The reason for the structure of the test-case code is that my app processes files in batches. Each file contains rows of data that need to be inserted into the equivalent of the test table. If an error occurs, the updates for all the files in that batch need to be rolled back, hence I do the batches in a single transaction. I need to know the result (success of failure) for each individual file. 1500 rows per file is pretty average. 15 files per batch is very low. In reality it's more like 100 files.

Without the FK reference the insert times are relatively linear...

drop table if exists test;
Update count: 0
(9 ms)

create table test (    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    col1 CHAR(1) DEFAULT 'N',
    col2fk1 BIGINT,
    col3 BIGINT,
    col4 BIGINT,
    col5 CHAR(1),
    col6 BIGINT,
    col7 BIGINT,
    col8 BIGINT,
    col9 BIGINT,
    col10 BIGINT,
    col11 VARCHAR(1000)
);
Update count: 0
(2 ms)

set autocommit off;
Update count: 0
(0 ms)


@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );
113 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );
104 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );
169 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );
112 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );
225 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );
150 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );
121 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );
110 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );
103 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );
109 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );
217 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );
120 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );
125 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );
145 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );
141 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

However, with an FK reference the time taken for each batch goes up quite steeply between batches...

drop table if exists fk1_table;
Update count: 0
(90 ms)

create table fk1_table ( id bigint primary key, ref_col bigint);
Update count: 0
(1 ms)

insert into fk1_table values(1, 1);
Update count: 1
(1 ms)


drop table if exists test;
Update count: 0
(1 ms)

create table test (    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    col1 CHAR(1) DEFAULT 'N',
    col2fk1 BIGINT,
    col3 BIGINT,
    col4 BIGINT,
    col5 CHAR(1),
    col6 BIGINT,
    col7 BIGINT,
    col8 BIGINT,
    col9 BIGINT,
    col10 BIGINT,
    col11 VARCHAR(1000)
);
Update count: 0
(0 ms)

ALTER TABLE test ADD FOREIGN KEY(col2fk1) REFERENCES fk1_table(ID);
Update count: 0
(1 ms)


set autocommit off;
Update count: 0
(1 ms)


@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );
220 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );
375 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );
579 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );
1215 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );
2040 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );
2831 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );
4358 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );
4013 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );
4458 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );
5020 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );
5558 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );
6124 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );
6846 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );
7244 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );
7738 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )


commit;
Update count: 0
(48564 ms)

Committing in between batches seems to drop the insert time again, but I can't do this in my app as I need to roll back all batches if an error occurs...

drop table if exists fk1_table;
Update count: 0
(7 ms)

create table fk1_table ( id bigint primary key, ref_col bigint);
Update count: 0
(1 ms)

insert into fk1_table values(1, 1);
Update count: 1
(1 ms)


drop table if exists test;
Update count: 0
(12 ms)

create table test (    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    col1 CHAR(1) DEFAULT 'N',
    col2fk1 BIGINT,
    col3 BIGINT,
    col4 BIGINT,
    col5 CHAR(1),
    col6 BIGINT,
    col7 BIGINT,
    col8 BIGINT,
    col9 BIGINT,
    col10 BIGINT,
    col11 VARCHAR(1000)
);
Update count: 0
(0 ms)

ALTER TABLE test ADD FOREIGN KEY(col2fk1) REFERENCES fk1_table(ID);
Update count: 0
(0 ms)


set autocommit off;
Update count: 0
(0 ms)


@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );
215 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );
386 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );
580 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );
1165 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );
1894 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

COMMIT;
Update count: 0
(2363 ms)

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );
234 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );
377 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );
610 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );
1044 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );
2167 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

COMMIT;
Update count: 0
(3202 ms)

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );
219 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );
410 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );
616 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );
1008 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );
1697 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

COMMIT;
Update count: 0
(2334 ms)

Cheers,
Ian.

[IanP]

More info: if mvcc=false the inserts are linear, if it's true they expand fast, so looks like it might be abug on the mvcc code.

With MVCC

url = jdbc:h2:tcp://localhost/~/sometest;MVCC=true

drop table if exists fk1_table;
Update count: 0

(6 ms)

create table fk1_table ( id bigint primary key, ref_col bigint);

Update count: 0
(1 ms)

insert into fk1_table values(1, 1);

Update count: 1
(0 ms)

drop table if exists test;

Update count: 0
(24 ms)

create table test (    ID BIGINT AUTO_INCREMENT PRIMARY KEY,

    col1 CHAR(1) DEFAULT 'N',
    col2fk1 BIGINT,
    col3 BIGINT,
    col4 BIGINT,
    col5 CHAR(1),
    col6 BIGINT,
    col7 BIGINT,
    col8 BIGINT,
    col9 BIGINT,
    col10 BIGINT,
    col11 VARCHAR(1000)
);
Update count: 0
(2 ms)

ALTER TABLE test ADD FOREIGN KEY(col2fk1) REFERENCES fk1_table(ID);

Update count: 0
(1 ms)


set autocommit off;

Update count: 0
(1 ms)


@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );

312 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );

534 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );

614 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );

1350 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );

1199 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );

2026 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );

2513 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );

3239 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );

3931 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );

4753 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );

5187 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );

5840 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );

6482 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );

7051 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );

7564 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

Without MVCC

url = jdbc:h2:tcp://localhost/~/sometest

drop table if exists fk1_table;
Update count: 0

(8 ms)

create table fk1_table ( id bigint primary key, ref_col bigint);

Update count: 0
(1 ms)

insert into fk1_table values(1, 1);

Update count: 1
(0 ms)

drop table if exists test;

Update count: 0
(27 ms)

create table test (    ID BIGINT AUTO_INCREMENT PRIMARY KEY,

    col1 CHAR(1) DEFAULT 'N',
    col2fk1 BIGINT,
    col3 BIGINT,
    col4 BIGINT,
    col5 CHAR(1),
    col6 BIGINT,
    col7 BIGINT,
    col8 BIGINT,
    col9 BIGINT,
    col10 BIGINT,
    col11 VARCHAR(1000)
);
Update count: 0

(1 ms)

ALTER TABLE test ADD FOREIGN KEY(col2fk1) REFERENCES fk1_table(ID);

Update count: 0
(1 ms)


set autocommit off;

Update count: 0

(0 ms)


@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );

123 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );

107 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );

113 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );

137 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );

116 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );

111 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );

100 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );

116 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );

120 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );

131 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' );

105 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 1, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' );

144 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 2, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' );

150 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 3, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' );

92 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 4, 'N', ?, ?, 1, 1, 1, 'a value' )

@LOOP 1500 INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' );

103 ms: 1500 * (Prepared) (i, i) INSERT INTO test(col1, col2fk1 , col3, col4, col5, col6, col7, col8, col9, col10, col11) values('N', 1, 1, 5, 'N', ?, ?, 1, 1, 1, 'a value' )

COMMIT;
Update count: 0
(0 ms)

[IanP]

Clearly not going to get a response here, so I've worked around the problem in my application code. However, for those using mvcc it's worth noting that insert times increase in non-linear manner to quickly become unacceptably slow when using prepared statements to do inserts in very small tables with foreign key references, as per the test case I've provided.

Maybe this is actually expected behaviour for mvcc rather than a bug. If so, it's expected behaviour that is very worth avoiding if you're using H2 in your appliction.

Cheers,
Ian.

[Noel Grandin]

MVCC is still a little experimental, so your mileage may vary.

If you could create a simple test-case, and use the profiler on it
http://www.h2database.com/html/performance.html?highlight=profiler&search=profiler#built_in_profiler
we might be able to help you.

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/4TbumZE3TcoJ.
>
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.