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)
--
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/-/0N8XuAgQE_0J.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
