subject:"\[h2\] why insert many record to in\-memory database is same speed as embedded\-mode"

Re: [h2] why insert many record to in-memory database is same speed as embedded-mode

2024-01-12 Thread mche...@gmail.com

thanks all

On Wednesday 10 January 2024 at 09:38:59 UTC+8 Andrei Tokar wrote:

> 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" 
>  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 mche...@gmail.com 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, mche...@gmail.com 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"

Re: [h2] why insert many record to in-memory database is same speed as embedded-mode

2024-01-09 Thread Andrei Tokar

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" 
 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 mche...@gmail.com 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, mche...@gmail.com 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"

Re: [h2] why insert many record to in-memory database is same speed as embedded-mode

2024-01-09 Thread mche...@gmail.com

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, mche...@gmail.com 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 < 1; 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

Re: [h2] why insert many record to in-memory database is same speed as embedded-mode

2024-01-08 Thread Andreas Reichel

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, mche...@gmail.com 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;PAGE_
> SIZE=10240;CACHE_TYPE=SOFT_LRU;", "sa", "");
> // Connection conn =
> DriverManager.getConnection("jdbc:h2:mem:test;CACHE_SIZE=13107200;PAG
> E_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 < 1; 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 da

[h2] why insert many record to in-memory database is same speed as embedded-mode

2024-01-08 Thread mche...@gmail.com

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;PAGE_SIZE=10240;CACHE_TYPE=SOFT_LRU;",
 
"sa", "");
// Connection conn = 
DriverManager.getConnection("jdbc:h2:mem:test;CACHE_SIZE=13107200;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 < 1; 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 h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/cd28e1d9-9dbd-45f9-b613-7c49566f307cn%40googlegroups.com.

Re: [h2] why insert many record to in-memory database is same speed as embedded-mode

Re: [h2] why insert many record to in-memory database is same speed as embedded-mode

Re: [h2] why insert many record to in-memory database is same speed as embedded-mode

Re: [h2] why insert many record to in-memory database is same speed as embedded-mode

[h2] why insert many record to in-memory database is same speed as embedded-mode

5 matches

Site Navigation

Mail list logo

Footer information