no, my program use 20 mins keep inserting to H2 (embed + in memory), both speed become stable and nearly the same.
On Tuesday 9 January 2024 at 01:30:44 UTC+8 Andreas Reichel wrote: > Because its cached and data are written to disk only eventually when the > cache is full? > > On Mon, 2024-01-08 at 09:20 -0800, [email protected] wrote: > > hi. > why insert many record to in-memory database is same speed as > embedded-mode. In-mem should be much faster, right? > > > import java.io.FileNotFoundException; > import java.io.IOException; > import java.sql.Connection; > import java.sql.DriverManager; > import java.sql.PreparedStatement; > import java.sql.SQLException; > import java.sql.Statement; > import me.tongfei.progressbar.ProgressBar; > import org.junit.Test; > > /** > * > * @author peter > */ > public class TestH2InsertSpeed { > > @Test > public void test() throws FileNotFoundException, IOException, SQLException > { > > Connection conn = DriverManager.getConnection("jdbc:h2:./test;CACHE_SIZE= > 13107200 <310-7200>;PAGE_SIZE=10240;CACHE_TYPE=SOFT_LRU;", "sa", ""); > // Connection conn = > DriverManager.getConnection("jdbc:h2:mem:test;CACHE_SIZE=13107200 > <310-7200>;PAGE_SIZE=10240;CACHE_TYPE=SOFT_LRU;"); > Statement stmt2 = conn.createStatement(); > stmt2.execute("drop table if exists qemu;"); > stmt2.execute(""" > CREATE TABLE "PUBLIC"."QEMU" > ( > "ID" integer auto_increment PRIMARY KEY NOT NULL, > "SEQUENCE" bigint, > "DATE" timestamp, > "COMPUTER" varchar(50), > "INTERRUPT" boolean, > "INTERRUPT_CAUSE" bigint, > "INTERRUPT_DESC" varchar(30), > "PC" bigint NOT NULL, > "MHARTID" bigint NOT NULL, > "MSTATUS" bigint NOT NULL, > "HSTATUS" bigint NOT NULL, > "VSSTATUS" bigint NOT NULL, > "MIP" bigint NOT NULL, > "MIE" bigint NOT NULL, > "MIDELEG" bigint NOT NULL, > "HIDELEG" bigint NOT NULL, > "MEDELEG" bigint NOT NULL, > "HEDELEG" bigint NOT NULL, > "MTVEC" bigint NOT NULL, > "STVEC" bigint NOT NULL, > "VSTVEC" bigint NOT NULL, > "MEPC" bigint NOT NULL, > "SEPC" bigint NOT NULL, > "VSEPC" bigint NOT NULL, > "MCAUSE" bigint NOT NULL, > "SCAUSE" bigint NOT NULL, > "VSCAUSE" bigint NOT NULL, > "MTVAL" bigint NOT NULL, > "STVAL" bigint NOT NULL, > "HTVAL" bigint NOT NULL, > "MTVAL2" bigint NOT NULL, > "MSCRATCH" bigint NOT NULL, > "SSCRATCH" bigint NOT NULL, > "SATP" bigint NOT NULL, > "X0_ZERO" bigint NOT NULL, > "X1_RA" bigint NOT NULL, > "X2_SP" bigint NOT NULL, > "X3_GP" bigint NOT NULL, > "X4_TP" bigint NOT NULL, > "X5_T0" bigint NOT NULL, > "X6_T1" bigint NOT NULL, > "X7_T2" bigint NOT NULL, > "X8_S0" bigint NOT NULL, > "X9_S1" bigint NOT NULL, > "X10_A0" bigint NOT NULL, > "X11_A1" bigint NOT NULL, > "X12_A2" bigint NOT NULL, > "X13_A3" bigint NOT NULL, > "X14_A4" bigint NOT NULL, > "X15_A5" bigint NOT NULL, > "X16_A6" bigint NOT NULL, > "X17_A7" bigint NOT NULL, > "X18_S2" bigint NOT NULL, > "X19_S3" bigint NOT NULL, > "X20_S4" bigint NOT NULL, > "X21_S5" bigint NOT NULL, > "X22_S6" bigint NOT NULL, > "X23_S7" bigint NOT NULL, > "X24_S8" bigint NOT NULL, > "X25_S9" bigint NOT NULL, > "X26_S10" bigint NOT NULL, > "X27_S11" bigint NOT NULL, > "X28_T3" bigint NOT NULL, > "X29_T4" bigint NOT NULL, > "X30_T5" bigint NOT NULL, > "X31_T6" bigint NOT NULL, > "LINENO" bigint, > "CODE" varchar(200), > "MEM" boolean, > "MEMOPERATION" varchar(200), > "MEMREAD" boolean, > "MEMADDR" bigint, > "MEMVALUE" bigint, > "MEMSIZE" integer, > "CCODE" varchar(100), > "PRIV" integer, > "IRQREQUEST" boolean, > "IRQREQUESTNO" integer, > "IRQREQUESTLEVEL" integer > );"""); > > String sql = "INSERT INTO `qemu` VALUES (default, ?, CURRENT_TIMESTAMP(), > 'quantr-ubuntu', ?, ?, ?"; > sql += ",?".repeat(59); > sql += ",?,?,?,?,?,?,?,?,?,?,?,?,?)"; > PreparedStatement stmt = conn.prepareStatement(sql); > > ProgressBar pb = new ProgressBar("Insert H2", 1000); > for (int x1 = 0; x1 < 1000; x1++) { > // System.out.println(x1); > for (int x2 = 0; x2 < 10000; x2++) { > int x = 1; > stmt.setLong(x++, 12345678l); > stmt.setBoolean(x++, true); > stmt.setLong(x++, 12345678l); > stmt.setString(x++, "aslkdjads alksdja ldj"); > for (int z = 0; z < 59; z++) { > // System.out.println(me.getKey() + "\t= " + me.getValue()); > // System.out.println(">" + me.getKey() + "=" + me.getValue()); > stmt.setLong(x++, 12345678l); > } > stmt.setLong(x++, 12345678l); > stmt.setString(x++, "askdjasd aljdlasjdlkaj sd"); > stmt.setBoolean(x++, true); > stmt.setString(x++, "peter cheung"); > stmt.setBoolean(x++, false); > stmt.setLong(x++, 12345678l); > stmt.setLong(x++, 12345678l); > stmt.setInt(x++, 12345678); > stmt.setString(x++, "asdasd as daasd asda sds das d"); > stmt.setInt(x++, 12345678); > stmt.setBoolean(x++, true); > stmt.setInt(x++, 12345678); > stmt.setInt(x++, 12345678); > stmt.addBatch(); > } > stmt.executeBatch(); > pb.step(); > } > pb.refresh(); > conn.commit(); > conn.close(); > } > } > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/cd28e1d9-9dbd-45f9-b613-7c49566f307cn%40googlegroups.com > > <https://groups.google.com/d/msgid/h2-database/cd28e1d9-9dbd-45f9-b613-7c49566f307cn%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/22c8ad86-b0de-466e-a807-bfc0a0853e0bn%40googlegroups.com.
