Apparently, you assumption about "In-mem should be much faster" is not correct. It just makes me wonder, what exactly is the problem at hand? Is that the disk-based database is too fast? Or there is an expectation of some "ludicrous speed" <https://tvtropes.org/pmwiki/pmwiki.php/Main/LudicrousSpeed> for in-memory mode? 8-) And if speed is not ludicrous enough - compare it with other databases out there. I am genuinely interested to see some results posted here. Bottleneck most likely is memory manipulation, related to transactions management and non-blocking B-tree access (multi-version store). Disk writes are done by parallel background threads, and it looks like, they are pretty much free (in this use case). Some significant short-cuts can be taken under assumption of a single-client use (non-transactional, non-concurrent), but that is the topic for a wish list.
On Tuesday, January 9, 2024 at 8:26:58 AM UTC-5 [email protected] wrote: > 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/b543e4a2-6385-4f2b-9677-6b06450a87bfn%40googlegroups.com.
