궁금해서 임베디드 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();
}
}
