2010年4月10日土曜日

SQLiteとH2の性能比較

この投稿へのリンク
H2とSQLite3の性能比較が知りたくて調べてみたが
古い記事しかなかったので簡単に性能比較してみた。

SQLiteバージョン : 3.6.23
H2バージョン : 1.2.132

テスト言語はJava(プロジェクトがJavaベースなので)
SQLiteのJDBCドライバは「SQLiteJDBC」を使用した。

SQLiteJDBC : http://www.zentus.com/sqlitejdbc/

テスト環境1


- CPU : 2.53 GHz Intel Core 2 Duo
- Memory : 4GB
- OS : Mac OS X 10.6.3
区分SQLiteH2 EmbeddedH2 In-memory
Insert (100万件)0.5 秒0.9 秒0.9 秒
Select (100万件)0.002 秒0.015 秒0.07 秒
File size2,048 bytes14,336 bytes-


テスト環境2


- CPU : 2.53 GHz Intel Core 2 Duo
- Memory : 4GB
- OS : Mac OS X 10.6.3
区分SQLiteH2 EmbeddedH2 In-memory
Insert (100万件)0.5 秒1.3 秒1.5 秒
Select (100万件)0.004 秒0.09 秒0.095 秒
File size2,048 bytes14,336 bytes-


簡単なテストだが全板的にSQLiteの勝ち。
特に瀬能が低いマシンではもっとH2よりパフォーマンスがいいらしい。
でもH2は今まで「In-memory」方式が「組込み」より早いと思ったのが大間違いだったのがショックだ。


下はテストで使用したソース

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class SqlitePerformance {
private static final int ROW_COUNT = 1000000;
private static final int MAX = 100;

/**
* SQLite3 Embedded
*
* @author babukuma
* @since 2010/04/09
* @param args
*/
public static void main(String[] args) throws Exception {
System.out.println("SQLite TEST [" + ROW_COUNT + "件]");
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager
.getConnection("jdbc:sqlite:babukuma.sqllite.db");

Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists babukuma;");
stat
.executeUpdate("create table babukuma (id integer primary key, value integer);");

conn.setAutoCommit(false);
System.out.print("Insert Test : ");
long startTime = System.currentTimeMillis();

PreparedStatement prep = conn
.prepareStatement("insert into babukuma values (?, ?);");

for (int i = 0; i < ROW_COUNT; i++) {
prep.setInt(1, i);
prep.setInt(2, i);
prep.addBatch();

if (i > 0 && (i % MAX) == 0 && i == ROW_COUNT - 1) {
prep.executeBatch();
prep.clearBatch();
}
}

conn.commit();

long endTime = System.currentTimeMillis();
System.out.println(((endTime - startTime) / 1000.) + "秒");

System.out.print("Select Test : ");
startTime = System.currentTimeMillis();
ResultSet rs = stat.executeQuery("select * from babukuma;");
while (rs.next()) {
rs.getInt("id");
rs.getInt("value");
// System.out.println("id = " + rs.getInt("id"));
// System.out.println("value = " + rs.getString("value"));
}
rs.close();
endTime = System.currentTimeMillis();
System.out.println(((endTime - startTime) / 1000.) + "秒");

conn.close();
}
}


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.h2.jdbcx.JdbcConnectionPool;

public class H2Performance {
private static final int ROW_COUNT = 1000000;
private static final int MAX = 100;

/**
* H2 Embedded
*
* @author babukuma
* @since 2010/04/09
* @param args
*/
public static void main(String[] args) throws Exception {
System.out.println("H2 TEST [" + ROW_COUNT + "件]");
JdbcConnectionPool cp = JdbcConnectionPool.create(
"jdbc:h2:babukuma.h2", "", "");
Connection conn = cp.getConnection();

Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists babukuma;");
stat
.executeUpdate("create table babukuma (id int primary key, value int);");

conn.setAutoCommit(false);
System.out.print("Insert Test : ");
long startTime = System.currentTimeMillis();

PreparedStatement prep = conn
.prepareStatement("insert into babukuma values (?, ?);");

for (int i = 0; i < ROW_COUNT; i++) {
prep.setInt(1, i);
prep.setInt(2, i);
prep.addBatch();

if (i > 0 && (i % MAX) == 0 && i == ROW_COUNT - 1) {
prep.executeBatch();
prep.clearBatch();
}
}

conn.commit();

long endTime = System.currentTimeMillis();
System.out.println(((endTime - startTime) / 1000.) + "秒");

System.out.print("Select Test : ");
startTime = System.currentTimeMillis();
ResultSet rs = stat.executeQuery("select * from babukuma;");
while (rs.next()) {
rs.getInt("id");
rs.getInt("value");
// System.out.println("id = " + rs.getInt("id"));
// System.out.println("value = " + rs.getString("value"));
}
rs.close();
endTime = System.currentTimeMillis();
System.out.println(((endTime - startTime) / 1000.) + "秒");

conn.close();
}
}


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.h2.jdbcx.JdbcConnectionPool;

public class H2InMemoryPerformance {
private static final int ROW_COUNT = 1000000;
private static final int MAX = 100;

/**
* H2 In memory
*
* @author babukuma
* @since 2010/04/09
* @param args
*/
public static void main(String[] args) throws Exception {
System.out.println("H2 In Memory TEST [" + ROW_COUNT + "件]");
JdbcConnectionPool cp = JdbcConnectionPool
.create("jdbc:h2:mem:", "", "");
Connection conn = cp.getConnection();

Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists babukuma;");
stat
.executeUpdate("create table babukuma (id int primary key, value int);");

conn.setAutoCommit(false);
System.out.print("Insert Test : ");
long startTime = System.currentTimeMillis();

PreparedStatement prep = conn
.prepareStatement("insert into babukuma values (?, ?);");

for (int i = 0; i < ROW_COUNT; i++) {
prep.setInt(1, i);
prep.setInt(2, i);
prep.addBatch();

if (i > 0 && (i % MAX) == 0 && i == ROW_COUNT - 1) {
prep.executeBatch();
prep.clearBatch();
}
}

conn.commit();

long endTime = System.currentTimeMillis();
System.out.println(((endTime - startTime) / 1000.) + "秒");

System.out.print("Select Test : ");
startTime = System.currentTimeMillis();
ResultSet rs = stat.executeQuery("select * from babukuma;");
while (rs.next()) {
rs.getInt("id");
rs.getInt("value");
// System.out.println("id = " + rs.getInt("id"));
// System.out.println("value = " + rs.getString("value"));
}
rs.close();
endTime = System.currentTimeMillis();
System.out.println(((endTime - startTime) / 1000.) + "秒");

conn.close();
}
}

2010年4月9日金曜日

SQLite과 H2의 성능비교

この投稿へのリンク
궁금해서 임베디드 DB인 H2와 SQLite3를 비교해보았다.

SQLite 버젼 : 3.6.23
H2 버젼 : 1.2.132

둘다 사용할 프로젝트가 Java인 관계로 언어는 Java로.
SQLite는 JDBC드라이버인 SQLiteJDBC를 사용하였다.

SQLiteJDBC : http://www.zentus.com/sqlitejdbc/

테스트 환경 1


- CPU : 2.53 GHz Intel Core 2 Duo
- Memory : 4GB
- OS : Mac OS X 10.6.3
구분SQLiteH2 EmbeddedH2 In-memory
Insert (100만건)0.5 초0.9 초0.9 초
Select (100만건)0.002 초0.015 초0.07 초
File size2,048 bytes14,336 bytes-


테스트 환경 2


- CPU : 2.53 GHz Intel Core 2 Duo
- Memory : 4GB
- OS : Mac OS X 10.6.3
구분SQLiteH2 EmbeddedH2 In-memory
Insert (100만건)0.5 초1.3 초1.5 초
Select (100만건)0.004 초0.09 초0.095 초
File size2,048 bytes14,336 bytes-


비록 간단한 테스트이지만 대략적으로 SQLite가 우세한 듯 보인다.
특히 성능이 낮은 머신에서 H2와의 성능차이가 뚜렷하다.
그리고 H2는 여지껏 「In-memory」방식이 「임베디드」방식보다 성능이 더 좋을 줄 알았는데,
전혀 생각과 틀려 충격이었다.


테스트용 소스는 아래와 같다.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class SqlitePerformance {
private static final int ROW_COUNT = 1000000;
private static final int MAX = 100;

/**
* SQLite3 Embedded
*
* @author babukuma
* @since 2010/04/09
* @param args
*/
public static void main(String[] args) throws Exception {
System.out.println("SQLite TEST [" + ROW_COUNT + "件]");
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager
.getConnection("jdbc:sqlite:babukuma.sqllite.db");

Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists babukuma;");
stat
.executeUpdate("create table babukuma (id integer primary key, value integer);");

conn.setAutoCommit(false);
System.out.print("Insert Test : ");
long startTime = System.currentTimeMillis();

PreparedStatement prep = conn
.prepareStatement("insert into babukuma values (?, ?);");

for (int i = 0; i < ROW_COUNT; i++) {
prep.setInt(1, i);
prep.setInt(2, i);
prep.addBatch();

if (i > 0 && (i % MAX) == 0 && i == ROW_COUNT - 1) {
prep.executeBatch();
prep.clearBatch();
}
}

conn.commit();

long endTime = System.currentTimeMillis();
System.out.println(((endTime - startTime) / 1000.) + "秒");

System.out.print("Select Test : ");
startTime = System.currentTimeMillis();
ResultSet rs = stat.executeQuery("select * from babukuma;");
while (rs.next()) {
rs.getInt("id");
rs.getInt("value");
// System.out.println("id = " + rs.getInt("id"));
// System.out.println("value = " + rs.getString("value"));
}
rs.close();
endTime = System.currentTimeMillis();
System.out.println(((endTime - startTime) / 1000.) + "秒");

conn.close();
}
}


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.h2.jdbcx.JdbcConnectionPool;

public class H2Performance {
private static final int ROW_COUNT = 1000000;
private static final int MAX = 100;

/**
* H2 Embedded
*
* @author babukuma
* @since 2010/04/09
* @param args
*/
public static void main(String[] args) throws Exception {
System.out.println("H2 TEST [" + ROW_COUNT + "件]");
JdbcConnectionPool cp = JdbcConnectionPool.create(
"jdbc:h2:babukuma.h2", "", "");
Connection conn = cp.getConnection();

Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists babukuma;");
stat
.executeUpdate("create table babukuma (id int primary key, value int);");

conn.setAutoCommit(false);
System.out.print("Insert Test : ");
long startTime = System.currentTimeMillis();

PreparedStatement prep = conn
.prepareStatement("insert into babukuma values (?, ?);");

for (int i = 0; i < ROW_COUNT; i++) {
prep.setInt(1, i);
prep.setInt(2, i);
prep.addBatch();

if (i > 0 && (i % MAX) == 0 && i == ROW_COUNT - 1) {
prep.executeBatch();
prep.clearBatch();
}
}

conn.commit();

long endTime = System.currentTimeMillis();
System.out.println(((endTime - startTime) / 1000.) + "秒");

System.out.print("Select Test : ");
startTime = System.currentTimeMillis();
ResultSet rs = stat.executeQuery("select * from babukuma;");
while (rs.next()) {
rs.getInt("id");
rs.getInt("value");
// System.out.println("id = " + rs.getInt("id"));
// System.out.println("value = " + rs.getString("value"));
}
rs.close();
endTime = System.currentTimeMillis();
System.out.println(((endTime - startTime) / 1000.) + "秒");

conn.close();
}
}


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.h2.jdbcx.JdbcConnectionPool;

public class H2InMemoryPerformance {
private static final int ROW_COUNT = 1000000;
private static final int MAX = 100;

/**
* H2 In memory
*
* @author babukuma
* @since 2010/04/09
* @param args
*/
public static void main(String[] args) throws Exception {
System.out.println("H2 In Memory TEST [" + ROW_COUNT + "件]");
JdbcConnectionPool cp = JdbcConnectionPool
.create("jdbc:h2:mem:", "", "");
Connection conn = cp.getConnection();

Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists babukuma;");
stat
.executeUpdate("create table babukuma (id int primary key, value int);");

conn.setAutoCommit(false);
System.out.print("Insert Test : ");
long startTime = System.currentTimeMillis();

PreparedStatement prep = conn
.prepareStatement("insert into babukuma values (?, ?);");

for (int i = 0; i < ROW_COUNT; i++) {
prep.setInt(1, i);
prep.setInt(2, i);
prep.addBatch();

if (i > 0 && (i % MAX) == 0 && i == ROW_COUNT - 1) {
prep.executeBatch();
prep.clearBatch();
}
}

conn.commit();

long endTime = System.currentTimeMillis();
System.out.println(((endTime - startTime) / 1000.) + "秒");

System.out.print("Select Test : ");
startTime = System.currentTimeMillis();
ResultSet rs = stat.executeQuery("select * from babukuma;");
while (rs.next()) {
rs.getInt("id");
rs.getInt("value");
// System.out.println("id = " + rs.getInt("id"));
// System.out.println("value = " + rs.getString("value"));
}
rs.close();
endTime = System.currentTimeMillis();
System.out.println(((endTime - startTime) / 1000.) + "秒");

conn.close();
}
}