Hi again,
I have created a simple test case (JUnit 4 and H2 must be on the
classpath):
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.jdbcx.JdbcConnectionPool;
import org.h2.jdbcx.JdbcDataSource;
import org.junit.BeforeClass;
import org.junit.Test;
public class DatabaseH2PoolTest {
private static JdbcDataSource ds;
private static JdbcConnectionPool connectionPool;
@BeforeClass
public static void createDatabaseInTemp() {
ds = new JdbcDataSource();
ds.setURL("jdbc:h2:c:/temp/testdatah2;TRACE_LEVEL_SYSTEM_OUT=3");
ds.setUser("");
ds.setPassword("");
connectionPool = JdbcConnectionPool.create(ds);
connectionPool.setMaxConnections(1);
createTable();
}
private static void createTable() {
Connection connection = null;
Statement stat = null;
try {
connection = connectionPool.getConnection();
stat = connection.createStatement();
// clear
stat.execute("DROP INDEX IF EXISTS IX_NODE_PARENT");
stat.execute("DROP TABLE IF EXISTS NODE");
// new tables
stat
.execute("CREATE TABLE IF NOT EXISTS
NODE(ID INTEGER NOT NULL
AUTO_INCREMENT(2) PRIMARY KEY, ID_PARENT INTEGER NOT NULL, KEY
VARCHAR, VALUE OTHER)");
stat.execute("CREATE INDEX IF NOT EXISTS IX_NODE_PARENT
ON
NODE(ID_PARENT)");
} catch (Exception e) {
throw new RuntimeException("Cannot create tables with
DDL script",
e);
} finally {
if (stat != null)
try {
stat.close();
} catch (SQLException e) {
throw new RuntimeException("Cannot
close statement", e);
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException("Cannot
close connection", e);
}
}
}
}
@Test
public void someSelectsOneThreadWith() {
long time0 = System.nanoTime();
for (int i = 0; i < 10000; i++) {
findNode(i, true);
}
long time = System.nanoTime() - time0;
System.out.println("With pool (ms): " + (time / 1000000));
}
@Test
public void someSelectsOneThreadWithout() {
long time0 = System.nanoTime();
for (int i = 0; i < 10000; i++) {
findNode(i, false);
}
long time = System.nanoTime() - time0;
System.out.println("Without pool (ms): " + (time / 1000000));
}
// dummy method - no result is returned :-)
public void findNode(int id, boolean usePool) {
Connection connection = null;
PreparedStatement stat = null;
try {
if (usePool)
connection = connectionPool.getConnection();
else
connection = ds.getConnection();
stat = connection.prepareStatement("SELECT * FROM NODE
WHERE ID
= ?");
stat.setInt(1, id);
ResultSet rs = stat.executeQuery();
if (rs.next()) {
rs.getInt("ID"); // dummy call
}
} catch (Exception e) {
throw new RuntimeException("Cannot execute SQL", e);
} finally {
if (stat != null)
try {
stat.close();
} catch (SQLException e) {
throw new RuntimeException("Cannot
close statement", e);
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException("Cannot
close connection", e);
}
}
}
}
}
Here is the log for the someSelectsOneThreadWith test (only 2 loops).
Even when the pool is used I can see disconnected/connected messages
in the trace output:
10-20 10:49:33 database: opening C:\TEMP\testdatah2 (build 100)
10-20 10:49:33 fileLock: save {method=file,
id=11d1971e3a38e830b7397db585033cc2c8fd940ea37}
10-20 10:49:33 fileLock: load {method=file,
id=11d1971e3a38e830b7397db585033cc2c8fd940ea37}
10-20 10:49:33 index: open existing SYS rows: 12
10-20 10:49:33 index: open existing NODE rows: 0
10-20 10:49:33 lock: 1 exclusive write lock added for NODE
10-20 10:49:33 lock: 1 exclusive write lock added for SYS
10-20 10:49:33 lock: 1 exclusive write lock unlock NODE
10-20 10:49:33 lock: 1 exclusive write lock unlock SYS
10-20 10:49:33 lock: 1 exclusive write lock added for NODE
10-20 10:49:33 lock: 1 exclusive write lock added for SYS
10-20 10:49:33 lock: 1 exclusive write lock unlock NODE
10-20 10:49:33 lock: 1 exclusive write lock unlock SYS
10-20 10:49:33 lock: 1 exclusive write lock added for NODE
10-20 10:49:33 lock: 1 exclusive write lock unlock NODE
10-20 10:49:33 database: opened C:\TEMP\testdatah2
10-20 10:49:33 session: connecting #2 to C:\TEMP\testdatah2
10-20 10:49:33 jdbc[2]:
/*SQL */SET TRACE_LEVEL_SYSTEM_OUT 3;
10-20 10:49:33 session: connected #2
10-20 10:49:33 jdbc[2]:
/**/Connection conn0 = DriverManager.getConnection("jdbc:h2:c:/temp/
testdatah2;TRACE_LEVEL_SYSTEM_OUT=3", "", "")
10-20 10:49:33 session: connecting #3 to C:\TEMP\testdatah2
10-20 10:49:33 jdbc[3]:
/*SQL */SET TRACE_LEVEL_SYSTEM_OUT 3;
10-20 10:49:33 session: connected #3
10-20 10:49:33 jdbc[3]:
/**/Connection conn1 = DriverManager.getConnection("jdbc:h2:c:/temp/
testdatah2;TRACE_LEVEL_SYSTEM_OUT=3", "", "")
10-20 10:49:33 jdbc[3]:
/**/conn1.close();
10-20 10:49:33 jdbc[3]:
/*SQL */ROLLBACK;
10-20 10:49:33 session: disconnecting #3
10-20 10:49:33 session: disconnected #3
10-20 10:49:33 session: connecting #4 to C:\TEMP\testdatah2
10-20 10:49:33 jdbc[4]:
/*SQL */SET TRACE_LEVEL_SYSTEM_OUT 3;
10-20 10:49:33 session: connected #4
10-20 10:49:33 jdbc[4]:
/**/Connection conn2 = DriverManager.getConnection("jdbc:h2:c:/temp/
testdatah2;TRACE_LEVEL_SYSTEM_OUT=3", "", "")
10-20 10:49:33 jdbc[4]:
/**/Statement stat0 = conn2.createStatement();
10-20 10:49:33 jdbc[4]:
/**/stat0.execute("DROP INDEX IF EXISTS IX_NODE_PARENT");
10-20 10:49:33 lock: 4 exclusive write lock added for SYS
10-20 10:49:33 jdbc[4]:
/*SQL */DROP INDEX IF EXISTS IX_NODE_PARENT;
10-20 10:49:33 lock: 4 exclusive write lock unlock SYS
10-20 10:49:33 jdbc[4]:
/**/stat0.execute("DROP TABLE IF EXISTS NODE");
10-20 10:49:33 lock: 4 exclusive write lock added for NODE
10-20 10:49:33 lock: 4 exclusive write lock added for SYS
10-20 10:49:33 jdbc[4]:
/*SQL */DROP TABLE IF EXISTS NODE;
10-20 10:49:33 lock: 4 exclusive write lock unlock SYS
10-20 10:49:33 jdbc[4]:
/**/stat0.execute("CREATE TABLE IF NOT EXISTS NODE(ID INTEGER NOT NULL
AUTO_INCREMENT(2) PRIMARY KEY, ID_PARENT INTEGER NOT NULL, KEY
VARCHAR, VALUE OTHER)");
10-20 10:49:33 lock: 4 exclusive write lock added for SYS
10-20 10:49:33 index: open existing NODE rows: 0
10-20 10:49:33 lock: 4 exclusive write lock unlock SYS
10-20 10:49:33 lock: 4 exclusive write lock added for NODE
10-20 10:49:33 index: Index PUBLIC.PRIMARY_KEY_2 head consistent=false
10-20 10:49:33 lock: 4 exclusive write lock added for SYS
10-20 10:49:33 jdbc[4]:
/*SQL */CREATE TABLE IF NOT EXISTS NODE(ID INTEGER NOT NULL
AUTO_INCREMENT(2) PRIMARY KEY, ID_PARENT INTEGER NOT NULL, KEY
VARCHAR, VALUE OTHER);
10-20 10:49:33 lock: 4 exclusive write lock unlock NODE
10-20 10:49:33 lock: 4 exclusive write lock unlock SYS
10-20 10:49:33 jdbc[4]:
/**/stat0.execute("CREATE INDEX IF NOT EXISTS IX_NODE_PARENT ON
NODE(ID_PARENT)");
10-20 10:49:33 lock: 4 exclusive write lock added for NODE
10-20 10:49:33 index: Index PUBLIC.IX_NODE_PARENT head
consistent=false
10-20 10:49:33 lock: 4 exclusive write lock added for SYS
10-20 10:49:33 jdbc[4]:
/*SQL */CREATE INDEX IF NOT EXISTS IX_NODE_PARENT ON NODE(ID_PARENT);
10-20 10:49:33 lock: 4 exclusive write lock unlock NODE
10-20 10:49:33 lock: 4 exclusive write lock unlock SYS
10-20 10:49:33 jdbc[4]:
/**/stat0.close();
10-20 10:49:33 jdbc[4]:
/**/conn2.close();
10-20 10:49:33 jdbc[4]:
/*SQL */ROLLBACK;
10-20 10:49:33 session: disconnecting #4
10-20 10:49:33 session: disconnected #4
10-20 10:49:33 session: connecting #5 to C:\TEMP\testdatah2
10-20 10:49:33 jdbc[5]:
/*SQL */SET TRACE_LEVEL_SYSTEM_OUT 3;
10-20 10:49:33 session: connected #5
10-20 10:49:33 jdbc[5]:
/**/Connection conn3 = DriverManager.getConnection("jdbc:h2:c:/temp/
testdatah2;TRACE_LEVEL_SYSTEM_OUT=3", "", "")
10-20 10:49:33 jdbc[5]:
/**/PreparedStatement prep0 = conn3.prepareStatement("SELECT * FROM
NODE WHERE ID = ?");
10-20 10:49:33 jdbc[5]:
/**/prep0.setInt(1, 0);
10-20 10:49:33 jdbc[5]:
/**/ResultSet rs4 = prep0.executeQuery();
10-20 10:49:33 jdbc[5]:
/*SQL l:31*/SELECT * FROM NODE WHERE ID = ? {1: 0};
10-20 10:49:33 jdbc[5]:
/**/rs4.next();
10-20 10:49:33 jdbc[5]:
/**/prep0.close();
10-20 10:49:33 jdbc[5]:
/**/conn3.close();
10-20 10:49:33 jdbc[5]:
/*SQL */ROLLBACK;
10-20 10:49:33 session: disconnecting #5
10-20 10:49:33 session: disconnected #5
10-20 10:49:33 session: connecting #6 to C:\TEMP\testdatah2
10-20 10:49:33 jdbc[6]:
/*SQL */SET TRACE_LEVEL_SYSTEM_OUT 3;
10-20 10:49:33 session: connected #6
10-20 10:49:33 jdbc[6]:
/**/Connection conn4 = DriverManager.getConnection("jdbc:h2:c:/temp/
testdatah2;TRACE_LEVEL_SYSTEM_OUT=3", "", "")
10-20 10:49:33 jdbc[6]:
/**/PreparedStatement prep1 = conn4.prepareStatement("SELECT * FROM
NODE WHERE ID = ?");
10-20 10:49:33 jdbc[6]:
/**/prep1.setInt(1, 1);
10-20 10:49:33 jdbc[6]:
/**/ResultSet rs5 = prep1.executeQuery();
10-20 10:49:33 jdbc[6]:
/*SQL l:31*/SELECT * FROM NODE WHERE ID = ? {1: 1};
10-20 10:49:33 jdbc[6]:
/**/rs5.next();
10-20 10:49:33 jdbc[6]:
/**/prep1.close();
With pool (ms): 34
10-20 10:49:33 database: closing C:\TEMP\testdatah2 from shutdown hook
10-20 10:49:33 session: disconnecting #2
10-20 10:49:33 session: disconnected #2
10-20 10:49:33 session: disconnecting #6
10-20 10:49:33 database: closing C:\TEMP\testdatah2
10-20 10:49:33 index: Index PUBLIC.PRIMARY_KEY_2 head consistent=true
10-20 10:49:33 index: Index PUBLIC.IX_NODE_PARENT head consistent=true
10-20 10:49:33 lock: 1 exclusive write lock added for SYS
10-20 10:49:33 lock: 1 exclusive write lock unlock SYS
10-20 10:49:33 fileLock: load {method=file,
id=11d1971e3a38e830b7397db585033cc2c8fd940ea37}
10-20 10:49:33 database: closed
10-20 10:49:33 session: disconnected #6
10-20 10:49:33 database: closing C:\TEMP\testdatah2
10-20 10:49:33 database: closed
The second interesting thing is the overhead of the connection pool
which is about 30 %:
With pool (ms): 1096
Without pool (ms): 773
This is the output of the test case (tracing was disabled). A
connection is made, simple select with no results is executed and the
connection is closed (is returned to the pool of 1 connection). Then
again .... 10 thousands times.
ps - I can send Eclipse project as an attachement if needed
LZ
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
