うまい言い回しが見つからないのでこんなタイトルになってしまったのだけど。要は、下記の擬似コードのような状態を指している。
for open connection execute sql close connection end for
open connection for execute sql end for close connection
一般的には、一本のコネクションで処理（複数のSQL）をすればよいため、前者の書き方はしない。また、これはありがちなパフォーマンス劣化事例として上げられることもしばしば目にする。
では、実際どの程度不利なのか、主に実行速度の面から検証をしてみる。
環境
計測その１
計測用プログラム
ループの「外」でコネクションの接続切断をするもの。実行するSQLはdual表を見に行くだけのものにして、極力、接続切断以外の影響を無くす。影響という点では、下記コードでは、厳密に言えばPreparedStatementも作りなおししないといけないんだけど、めんどくさいのでパス。
package connecttest; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class Test1 { public static void main(String[] args) throws SQLException { long start = System.currentTimeMillis(); try (Connection connection = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.0.20:1521:XE", "kagamihoge", "xxxx"); PreparedStatement sql = connection.prepareStatement("select 1 from dual");) { for (int i = 0; i < 100; i++) { ResultSet r = sql.executeQuery(); while (r.next()) { String s1 = r.getString(1); System.out.println(s1); } r.close(); } } long end = System.currentTimeMillis(); System.out.println(end - start); } }
ループの「内」でコネクションの接続切断をするもの。
package connecttest; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class Test2 { public static void main(String[] args) throws SQLException { long start = System.currentTimeMillis(); for (int i = 0; i < 100; i++) { try (Connection connection = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.0.20:1521:XE","kagamihoge", "xxxx"); PreparedStatement sql = connection.prepareStatement("select 1 from dual");) { ResultSet r = sql.executeQuery(); while (r.next()) { String s1 = r.getString(1); System.out.println(s1); } r.close(); } } long end = System.currentTimeMillis(); System.out.println(end - start); } }
計測結果
ループのカウントは100で実施。100以外でやった場合については感想とかの欄を参照。
| 1 | 2 | 3 | |
|---|---|---|---|
| 外 | 640 | 515 | 516 |
| 内 | 4703 | 4734 | 4750 |
感想とか
というわけで、ループの中で毎回接続切断してると、この環境ではそれだけで2〜3秒不利になった。100件でコレなので、もっと増えた場合を想像すると中々に恐ろしい。
ところで「内」の方のコードだけど、カウントを1000とかにすると下記の実行時例外が発生する。
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found
ぐぐってみると色々出てくるのだが、リスナーが返せるコネクションが無い状況だと発生するらしい。そうなる状況は当然色々考えられるのだけど、典型的な状況として、「内」のコードのように短時間に接続切断を繰り返しまくるのはダメである。
この環境のoracle xeはパラメータなんもいじってないので、下記SQLでprocessesを確認してみる。
select name, type, value from V$SYSTEM_PARAMETER where name = 'processes' or name = 'sessions'
と、processesは100になっているので、ウチの環境ではカウントを100より大きくすると例の例外が発生しやすくなるのは勘定が合う。
参考：
ORA-12519 - 解決!ORACLE!
as you like** ORA-12519: TNS: 適切なサービス・ハンドラが見つかりません。
計測その２
コネクションプーリング環境下ではどうなるのか？ という疑問が沸く。よって、Tomcat 7で同じ事をやってみる。
計測用プログラム、設定ファイルなど
server.xmlに追記
<Context docBase="connectionpooltest" path="/connectionpooltest" reloadable="true" source="org.eclipse.jst.jee.server:connectionpooltest"> <Resource name="jdbc/oraclexe" auth="Container" type="javax.sql.DataSource" driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@192.168.0.20:1521:XE" username="kagamihoge" password="xxxx" maxActive="20" maxIdle="10" maxWait="-1" /> </Context>
web.xmlに追記
<resource-ref> <res-ref-name>jdbc/oraclexe</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref>
ループの「外」でコネクションの接続切断をするもの。コピペで済ませたので、かなり長ったらしくなってしまった点はカンベン願いたい。
package hoge; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.sql.DataSource; @WebServlet("/Test1") public class Test1 extends HttpServlet { private static final long serialVersionUID = 1L; public Test1() { super(); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { long start = System.currentTimeMillis(); InitialContext context; try { context = new InitialContext(); DataSource dataSource = (DataSource) context.lookup("java:comp/env/jdbc/oraclexe"); try (Connection connection = dataSource.getConnection(); PreparedStatement sql = connection.prepareStatement("select 1 from dual");) { for (int i = 0; i < 100; i++) { ResultSet r = sql.executeQuery(); while (r.next()) { String s1 = r.getString(1); System.out.println(s1); } r.close(); } } catch (SQLException e) { e.printStackTrace(); } } catch (NamingException e) { e.printStackTrace(); } long end = System.currentTimeMillis(); System.out.println(end - start); } }
ループの「内」でコネクションの接続切断をするもの。
package hoge; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.sql.DataSource; @WebServlet("/Test2") public class Test2 extends HttpServlet { private static final long serialVersionUID = 1L; public Test2() { super(); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { long start = System.currentTimeMillis(); InitialContext context; try { context = new InitialContext(); DataSource dataSource = (DataSource) context.lookup("java:comp/env/jdbc/oraclexe"); for (int i = 0; i < 100; i++) { try (Connection connection = dataSource.getConnection(); PreparedStatement sql = connection.prepareStatement("select 1 from dual");) { ResultSet r = sql.executeQuery(); while (r.next()) { String s1 = r.getString(1); System.out.println(s1); } r.close(); } catch (SQLException e) { e.printStackTrace(); } } } catch (NamingException e) { e.printStackTrace(); } long end = System.currentTimeMillis(); System.out.println(end - start); } }
計測結果
| カウント | 種類 | 1 | 2 | 3 |
|---|---|---|---|---|
| 100 | 外 | 78 | 47 | 47 |
| 100 | 内 | 79 | 93 | 93 |
| 1000 | 外 | 469 | 407 | 390 |
| 1000 | 内 | 532 | 500 | 484 |
| 10000 | 外 | 3578 | 3485 | 3594 |
| 10000 | 内 | 4594 | 4500 | 4641 |
| 100000 | 外 | 35468 | 35000 | 35203 |
| 100000 | 内 | 45812 | 45516 | 45922 |
感想とか
コネクションプーリング環境下では、ループ内で接続切断の作りであっても、そこまでは速度劣化は見られない結果になった。とはいっても、不利なことには変わりは無く、カウントを増やしていくと差が出るようになっていく。
仮にコネクションプールから取得するとき、毎回同一のコネクションが必ず返るとする。その場合でも、プールからコネクションを取得するためのオーバーヘッドは生じるので、そこが速度差に繋がっているのではないか、と考えられる。
というわけで、常識的には、ループの内側でコネクションの接続・切断を繰り返すコードは書かないほうが良い、と思います。
