SQLiteとH2の性能比較

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

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

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

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

テスト環境１

- CPU : 2.53 GHz Intel Core 2 Duo
- Memory : 4GB
- OS : Mac OS X 10.6.3

区分	SQLite	H2 Embedded	H2 In-memory
Insert (100万件)	0.5 秒	0.9 秒	0.9 秒
Select (100万件)	0.002 秒	0.015 秒	0.07 秒
File size	2,048 bytes	14,336 bytes	-

テスト環境２

- CPU : 2.53 GHz Intel Core 2 Duo
- Memory : 4GB
- OS : Mac OS X 10.6.3

区分	SQLite	H2 Embedded	H2 In-memory
Insert (100万件)	0.5 秒	1.3 秒	1.5 秒
Select (100万件)	0.004 秒	0.09 秒	0.095 秒
File size	2,048 bytes	14,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();
 }
}

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

구분	SQLite	H2 Embedded	H2 In-memory
Insert (100만건)	0.5 초	0.9 초	0.9 초
Select (100만건)	0.002 초	0.015 초	0.07 초
File size	2,048 bytes	14,336 bytes	-

테스트 환경 2

- CPU : 2.53 GHz Intel Core 2 Duo
- Memory : 4GB
- OS : Mac OS X 10.6.3

구분	SQLite	H2 Embedded	H2 In-memory
Insert (100만건)	0.5 초	1.3 초	1.5 초
Select (100만건)	0.004 초	0.09 초	0.095 초
File size	2,048 bytes	14,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();
 }
}