Greetings Juergen.
Unfortunately I can't reproduce your concern after implementing your
test in the quickest and dirtiest way, with lots of overhead.
It ran through in 1 minute and I did not see any slowdown while
iterating:
> Task :ETLBox:H2PerformanceTest.main()
Aug 29, 2023 6:13:20 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 0
Aug 29, 2023 6:13:20 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 1
Aug 29, 2023 6:13:20 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 2
....
Aug 29, 2023 6:14:07 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 296
Aug 29, 2023 6:14:07 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 297
Aug 29, 2023 6:14:07 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 298
Aug 29, 2023 6:14:08 PM com.manticore.etl.custom.H2PerformanceTest main
INFO: loop = 299
BUILD SUCCESSFUL in 1m 13s
What exactly means "after some seconds" vs. "after some minutes"?
Can you elaborate on the details please?
Best regards
Andreas
On Tue, 2023-08-29 at 03:53 -0700, Jürgen Pingel wrote:
> Thanks for the fix version 2.2.222.
> Don't know if it will work for me, because I run with that revision
> into a performance problem.
> One of my unit tests runs now extremely long and it seems that much
> memory get allocated.
> Can't send you the complete code but I isolate it to the SQL
> statements and looks like:
>
> CREATE TABLE IF NOT EXISTS A (number VARCHAR(128) not NULL, intent
> INT not NULL, objID VARCHAR(30) not NULL, objType SMALLINT not NULL,
> PRIMARY KEY (number,intent,objID,objType));
> CREATE INDEX IF NOT EXISTS A_IDX ON A(objType,objID,intent);
> CREATE INDEX IF NOT EXISTS A_type_IDX ON A (objType);
>
> for (int loop = 0, number = 0; loop < 300; ++loop){
> DROP TABLE IF EXISTS B;
> CREATE TABLE IF NOT EXISTS B (number VARCHAR(128) not NULL, intent
> INT not NULL, objID VARCHAR(30) not NULL, objType SMALLINT not NULL,
> PRIMARY KEY (number,intent,objID,objType));
> CREATE INDEX IF NOT EXISTS B_IDX ON B(objType,objID,intent);
>
> for (int i = 0; i < 100; ++i) {
> ++number;
> MERGE INTO B (number,intent,objID,objType) VALUES
> (Integer.toString(number),1, Integer.toString(number), 1)
> }
>
> DELETE FROM A T WHERE EXISTS (SELECT NULL from A S WHERE
> T.objID=S.objID AND T.objType=S.objType AND T.intent=S.intent AND
> T.number<>S.number)
> MERGE INTO A T USING (SELECT * FROM B) AS S ON T.objID=S.objID AND
> T.objType=S.objType AND T.intent=S.intent AND T.number=S.number WHEN
> NOT MATCHED THEN INSERT (objID, objType, number, intent) VALUES
> (S.objID, S.objType, S.number, S.intent);
> DROP TABLE B CASCADE
> }
>
> If run with the previous versions 1.4, 2.1 and 2.2.220 it's finished
> after some seconds.
> With the 2.2.222 it takes longer with each loop; don't finish after
> some minutes.
>
> Please take a look; thanks.
>
> Thanks,
> Jürgen
> Andreas Reichel schrieb am Mittwoch, 23. August 2023 um 14:12:42
> UTC+2:
> > On Wed, 2023-08-23 at 08:09 -0400, Andrei Tokar wrote:
> > > This is a patch release, and AFAIK there are no incompatibilities
> > > with
> > > on-disk format of v.2.2.220, so hopefully it can be used as drop
> > > in
> > > replacement for 2.2.220 (and only 2.2.220!).
> >
> >
> >
> > Thank you very much.
> > Just in case: We have amended H2MigrationTool for support of H2
> > 2.2.222 already.
> >
> > http://h2migrationtool.manticore-projects.com/
> >
> > Best regards
> > Andreas
> --
> 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/a9dde386-ec94-4beb-b13a-a2913f6e4311n%40googlegroups.com
> .
--
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/a7aba634cf7821a96bb648b63281a7043d3a7556.camel%40manticore-projects.com.
package com.manticore.etl.custom;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class H2PerformanceTest {
private final static Logger LOGGER = Logger.getLogger(H2PerformanceTest.class.getName());
public static void main(String[] args) throws ClassNotFoundException {
String[] sqlStr = {
"CREATE TABLE IF NOT EXISTS A (number VARCHAR(128) not NULL, intent INT not NULL, objID VARCHAR(30) not NULL, objType SMALLINT not NULL, PRIMARY KEY (number,intent,objID,objType));",
"CREATE INDEX IF NOT EXISTS A_IDX ON A(objType,objID,intent);",
"CREATE INDEX IF NOT EXISTS A_type_IDX ON A (objType);",
"DROP TABLE IF EXISTS B; ",
"CREATE TABLE IF NOT EXISTS B (number VARCHAR(128) not NULL, intent INT not NULL, objID VARCHAR(30) not NULL, objType SMALLINT not NULL, PRIMARY KEY (number,intent,objID,objType));",
"CREATE INDEX IF NOT EXISTS B_IDX ON B(objType,objID,intent);",
"DELETE FROM A T WHERE EXISTS (SELECT NULL from A S WHERE T.objID=S.objID AND T.objType=S.objType AND T.intent=S.intent AND T.number<>S.number);",
"MERGE INTO A T USING (SELECT * FROM B) AS S ON T.objID=S.objID AND T.objType=S.objType AND T.intent=S.intent AND T.number=S.number WHEN NOT MATCHED THEN INSERT (objID, objType, number, intent) VALUES (S.objID, S.objType, S.number, S.intent);",
"DROP TABLE B CASCADE"
};
Class.forName("org.h2.Driver");
try (Connection conn = DriverManager.getConnection(
"jdbc:h2:mem:",
"SA",
""
); Statement statement = conn.createStatement()) {
statement.execute(sqlStr[0]);
statement.execute(sqlStr[1]);
statement.execute(sqlStr[2]);
for (int loop = 0, number = 0; loop < 300; ++loop) {
statement.execute(sqlStr[3]);
statement.execute(sqlStr[4]);
statement.execute(sqlStr[5]);
for (int i = 0; i < 100; ++i) {
++number;
statement.execute("MERGE INTO B (number,intent,objID,objType) VALUES ('"
+ number
+ "',1, '"
+ number
+ "', 1);");
}
statement.execute(sqlStr[6]);
statement.execute(sqlStr[7]);
statement.execute(sqlStr[8]);
LOGGER.log(Level.INFO, "loop = " + loop);
}
} catch (Exception ex) {
LOGGER.log(Level.SEVERE, "Error in loop", ex);
}
}
}
