Hello,
First, many thanks for all your big work.
I have noticed that H2 version 1.3.161 has slower performance in
comparison with v1.2.147 while loading records in the memory mode.
Here is a simple test that inserts records to a table:
public static void testLoadingDataInMemoryMode() throws Exception {
Connection h2conn = DriverManager.getConnection("jdbc:h2:mem:");
Statement st = h2conn.createStatement();
int stringColumnsCount = 20; // count of string columns
String createTableQuery = "CREATE TABLE test(int_col INT, long_col
BIGINT, " +
"double_col DOUBLE, timestamp_col
TIMESTAMP, ";
for (int i = 0; i < stringColumnsCount; i++) {
createTableQuery += "string_col" + i + " VARCHAR(100)";
if (i < stringColumnsCount - 1)
createTableQuery += ", ";
}
createTableQuery += ")";
System.out.println(createTableQuery);
st.executeUpdate(createTableQuery);
String insertQuery = "INSERT INTO test VALUES(?, ?, ?, ?, ";
for (int i = 0; i < stringColumnsCount; i++) {
insertQuery += "?";
if (i < stringColumnsCount - 1)
insertQuery += ", ";
}
insertQuery += ")";
System.out.println(insertQuery);
PreparedStatement pst = h2conn.prepareStatement(insertQuery);
long time = System.currentTimeMillis();
for (int i = 0; i < 500000; i++) {
pst.setInt(1, i);
pst.setLong(2, i);
pst.setDouble(3, i);
pst.setTimestamp(4, new java.sql.Timestamp(new
java.util.Date().getTime()));
for (int j = 0; j < stringColumnsCount; j++) {
pst.setString(4 + j + 1,
"It is some text bla-bla-bla-bla-bla-bla-bla-bla-
bla" + i);
}
// pst.addBatch(); // uncomment it to use batch inserting
pst.execute(); // comment it to check clear performance without
doing insertions
}
// pst.executeBatch(); // uncomment it to use batch inserting
System.out.println("Test time = " + (System.currentTimeMillis() -
time) +
"\n");
pst.close();
h2conn.close();
}
Results of testing on Athlon XP 5600+, 2 GB RAM, Win XP, JDK 1.5
Number of string columns Clear time(without inserting to H2)
1.2.147 time 1.3.161 time
----------------------------------------------------------------------------------------------------------------------------
0 0,7
sec 3,4 sec 4,3 sec
1
1,9 5,2
6,3
2
2,8 6
7,3
3
3,6
6,8 7,9
4
4,5
8 8,5
5
4,9
8,7 9,2
10
8 12,7 14
20
15 20
21,8
a few conclusions:
1) As may be seen from a result table v1.3.161 is slower on 10-20% on
average.
2) H2 spends 3-4 seconds to insert already created Java instances to
its database structure. Does H2 perform any additional preparations
for Java strings to be inserted to the in-memory database?
3) Batching not works as expected. addBatch() and executeBatch() does
not speed up the performance.
Do you have any ideas how to optimize inserting in memory mode?
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
