読者です 読者をやめる 読者になる 読者になる

kagamihogeの日記

kagamihogeの日記です。

ループの中でDBコネクション接続切断したときの速度劣化はどの程度か

Java Oracle

うまい言い回しが見つからないのでこんなタイトルになってしまったのだけど。要は、下記の擬似コードのような状態を指している。

for
  open connection
  execute sql
  close connection
end for
open connection
for
  execute sql
end for
close connection

一般的には、一本のコネクションで処理(複数のSQL)をすればよいため、前者の書き方はしない。また、これはありがちなパフォーマンス劣化事例として上げられることもしばしば目にする。

では、実際どの程度不利なのか、主に実行速度の面から検証をしてみる。

計測その1

計測用プログラム

ループの「外」でコネクションの接続切断をするもの。実行する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: 適切なサービス・ハンドラが見つかりません。

計測その2

コネクションプーリング環境下ではどうなるのか? という疑問が沸く。よって、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
感想とか

コネクションプーリング環境下では、ループ内で接続切断の作りであっても、そこまでは速度劣化は見られない結果になった。とはいっても、不利なことには変わりは無く、カウントを増やしていくと差が出るようになっていく。

仮にコネクションプールから取得するとき、毎回同一のコネクションが必ず返るとする。その場合でも、プールからコネクションを取得するためのオーバーヘッドは生じるので、そこが速度差に繋がっているのではないか、と考えられる。

というわけで、常識的には、ループの内側でコネクションの接続・切断を繰り返すコードは書かないほうが良い、と思います。