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 [email protected].
> 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 [email protected].
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);
}
}
}
