I'm seeing slow inserts due to an indexed column populated with random
data.
This is already explained here
https://groups.google.com/forum/?hl=da#!topic/h2-database/bGeXZXuEKDM
Now I have compared it with hsqldb which doesn't seem to have the same
performance issues.
Maybe hsqldb is creating spacing in the index so data not have to be moved
around?
Could this explain the differences and is possible to tweak H2 for better
performance?
*h2-1.3.170*
Testing jdbc:h2:C:\h2db
100.000 in 1482 (milliseconds)
100.000 in 2341
100.000 in 2639
100.000 in 3355
100.000 in 5726
100.000 in 3449
100.000 in 8006
100.000 in 13029
100.000 in 12902
100.000 in 12029
Selecting 100000
Select done in 4200
Close in 2388
*hsqldb-2.3.2*
Testing jdbc:hsqldb:file:c:\hsqldbtestdb
100.000 in 3838
100.000 in 6194
100.000 in 2922
100.000 in 3028
100.000 in 3401
100.000 in 3324
100.000 in 8254
100.000 in 3713
100.000 in 3839
100.000 in 3807
Selecting 100000
Select done in 3293
Close in 0
--
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 post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
package backup.tests;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Random;
import backup.Common;
public class HsqldbTest {
public static void main(String[] args) throws Exception
{
test("jdbc:h2:C:\\h2db");
test("jdbc:hsqldb:file:c:\\hsqldbtestdb");
}
public static void test(String jdbc) throws Exception
{
System.out.println("");
System.out.println("Testing " + jdbc);
Connection connection = DriverManager.getConnection(jdbc, "sa", "");
connection.createStatement().execute("CREATE CACHED TABLE file_chunck\r\n" +
"(\r\n" +
" id IDENTITY PRIMARY KEY\r\n" +
" , hash BINARY(16) NOT NULL \r\n" +
");");
connection.createStatement().execute("CREATE unique INDEX i_file_chunck_hash ON file_chunck (hash);");
long start_time = 0;
ArrayList<byte[]> toBeSelected = new ArrayList<byte[]>();
String sql = "insert into file_chunck (hash) values (?)";
PreparedStatement statement = connection.prepareStatement(sql);
for (int n=0; n<10; n++)
{
start_time = System.currentTimeMillis();
for (int i=0; i<100000; i++)
{
byte[] bytes = new byte[16];
new Random().nextBytes(bytes);
statement.setBytes(1, bytes);
statement.executeUpdate();
if (i % 10 == 0)
toBeSelected.add(bytes);
}
System.out.println("100.000 in " + (System.currentTimeMillis()-start_time));
}
sql = "select id, hash from file_chunck where hash = ?";
statement = connection.prepareStatement(sql);
System.out.println("Selecting " + toBeSelected.size());
start_time = System.currentTimeMillis();
Collections.shuffle(toBeSelected);
for (byte[] bytes : toBeSelected)
{
statement.setBytes(1, bytes);
ResultSet rs = statement.executeQuery();
if (!rs.next())
throw new Exception("Not found");
}
System.out.println("Select done in " + (System.currentTimeMillis()-start_time));
start_time = System.currentTimeMillis();
connection.close();
System.out.println("Close in " + (System.currentTimeMillis()-start_time));
}
}
