Oracle上でWHERE句内でBETWEEN句を利用した場合と利用しなかった場合の性能を測定してみた

SQLで指定した範囲内のデータを取得する際、BETWEEN句を利用できる場合は、BETWEEN句を利用した方が指定された範囲のデータを抽出す操作が1回で済むため、BETWEEN句を利用した方がよい。

今回は、WHERE句内でBETWEEN句を利用した場合と利用しなかった場合それぞれで、SQLの実行速度を測定するプログラムを作成してみたので、共有する。

なお、SQL文の性能改善を行う方法については、以下のサイトを参照のこと。
https://sites.google.com/site/orapeform/sql_minaoshi

前提条件

下記記事の実装が完了していること。

Oracle上でselect文で「*」を利用した場合とカラム名を利用した場合の性能を測定してみたSQL SELECT文を記載する際、「SELECT *」よりは「SELECT (カラム名)」を、「SELECT COUNT(*)」よりは...

また、USER_DATAテーブルに、上記記事で用意した100,000件のデータが設定されていること。

サンプルプログラムの作成

作成したサンプルプログラムの構成は、以下の通り。

なお、上記の赤枠は、前提条件のプログラムから変更したプログラムである。

Mapperインタフェース・Mapper XMLの内容は以下の通りで、BETWEEN句を利用しない場合・BETWEEN句を利用した場合それぞれで、生年月日が基準日前後10日間であるデータを取得するSQLを用意している。

package com.example.demo;

import org.apache.ibatis.annotations.Mapper;

import java.util.Date;
import java.util.List;

@Mapper
public interface UserDataMapper {

    /**
     * 指定したIDをもつユーザーデータテーブル(user_data)のデータを取得する
     * @param id ID
     * @return ユーザーデータテーブル(user_data)の指定したIDのデータ
     */
    UserData findById(Long id);

    /**
     * ユーザーデータテーブル(user_data)から生年月日が基準日前後10日間のデータを、
     * BETWEEN句を使わないで全件取得する
     * @param basicDate 基準日
     * @return ユーザーデータテーブル(user_data)のリスト
     */
    List<UserData> findByBirthdayNotBetween(Date basicDate);

    /**
     * ユーザーデータテーブル(user_data)から生年月日が基準日前後10日間のデータを、
     * BETWEEN句を使って全件取得する
     * @param basicDate 基準日
     * @return ユーザーデータテーブル(user_data)のリスト
     */
    List<UserData> findByBirthdayBetween(Date basicDate);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.UserDataMapper">
    <resultMap id="userDataResultMap" type="com.example.demo.UserData" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="birth_year" property="birthY" jdbcType="VARCHAR" />
        <result column="birth_month" property="birthM" jdbcType="VARCHAR" />
        <result column="birth_day" property="birthD" jdbcType="VARCHAR" />
        <result column="sex" property="sex" jdbcType="VARCHAR" />
        <result column="memo" property="memo" jdbcType="VARCHAR" />
    </resultMap>
    <select id="findById" parameterType="java.lang.Long" resultMap="userDataResultMap">
        SELECT id, name, birth_year, birth_month, birth_day, sex, memo 
        FROM USER_DATA WHERE id = #{id}
    </select>
    <select id="findByBirthdayNotBetween" 
            parameterType="java.util.Date" resultMap="userDataResultMap">
        SELECT id, name, birth_year, birth_month, birth_day, sex, memo FROM USER_DATA
        WHERE TO_DATE(TO_CHAR(birth_year) || LPAD(TO_CHAR(birth_month), 2, '0')
            || LPAD(TO_CHAR(birth_day), 2, '0'), 'yyyyMMdd') &gt;= (#{date} - 10)
        AND TO_DATE(TO_CHAR(birth_year) || LPAD(TO_CHAR(birth_month), 2, '0')
            || LPAD(TO_CHAR(birth_day), 2, '0'), 'yyyyMMdd') &lt;= (#{date} + 10)
    </select>
    <select id="findByBirthdayBetween" 
            parameterType="java.util.Date" resultMap="userDataResultMap">
        SELECT id, name, birth_year, birth_month, birth_day, sex, memo FROM USER_DATA
        WHERE TO_DATE(TO_CHAR(birth_year) || LPAD(TO_CHAR(birth_month), 2, '0')
            || LPAD(TO_CHAR(birth_day), 2, '0'), 'yyyyMMdd')
        BETWEEN (#{date} - 10) AND (#{date} + 10)
    </select>
</mapper>

また、サービスクラスのサブクラスの内容は以下の通りで、それぞれのSQLを呼び出し、それぞれの実行時間を表示するようにしている。

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.Calendar;
import java.util.List;

@Service
public class DemoServiceSub {

    /**
     * ユーザーデータテーブル(user_data)へアクセスするマッパー
     */
    @Autowired
    private UserDataMapper userDataMapper;

    /**
     * 性能検証を行うための個別サービス
     */
    public void verifyPerformanceEach() {
        // 初回DB接続時は時間がかかるため、あえて性能測定対象外のSQLを1度実行しておく
        userDataMapper.findById(Long.valueOf(1));

        System.out.println("--- ユーザーデータテーブル(user_data)から生年月日が基準日前後10日間"
                + "のデータを、BETWEEN句を使わないで全件取得するSQLを実行します start. ---");

        // 処理前の現在時刻を取得
        long startTime = System.currentTimeMillis();

        // 生年月日が基準日(2012/1/20)前後10日間のデータを、BETWEEN句を使わないで全件取得する
        Calendar cal = Calendar.getInstance();
        cal.set(2012, 0, 20);
        List<UserData> userDataList 
            = userDataMapper.findByBirthdayNotBetween(cal.getTime());

        // 処理後の現在時刻を取得
        long endTime = System.currentTimeMillis();

        System.out.println("取得件数 : " + userDataList.size());
        System.out.println("処理時間：" + (endTime - startTime) + " ms");

        System.out.println("--- ユーザーデータテーブル(user_data)から生年月日が基準日前後10日間"
                + "のデータを、BETWEEN句を使わないで全件取得するSQLを実行します end. ---");
        System.out.println();

        System.out.println("--- ユーザーデータテーブル(user_data)から生年月日が基準日前後10日間"
                + "のデータを、BETWEEN句を使って全件取得するSQLを実行します start. ---");

        // 処理前の現在時刻を取得
        startTime = System.currentTimeMillis();

        // 生年月日が基準日(2012/1/20)前後10日間のデータを、BETWEEN句を使って全件取得する
        userDataList = userDataMapper.findByBirthdayBetween(cal.getTime());

        // 処理後の現在時刻を取得
        endTime = System.currentTimeMillis();

        System.out.println("取得件数 : " + userDataList.size());
        System.out.println("処理時間：" + (endTime - startTime) + " ms");

        System.out.println("--- ユーザーデータテーブル(user_data)から生年月日が基準日前後10日間"
                + "のデータを、BETWEEN句を使って全件取得するSQLを実行します end. ---");
    }
}

その他のソースコード内容は、以下のサイトを参照のこと。
https://github.com/purin-it/java/tree/master/spring-boot-oracle-performance-between/demo