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を用意している。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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); } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | <?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') >= (#{date} - 10) AND TO_DATE(TO_CHAR(birth_year) || LPAD(TO_CHAR(birth_month), 2, '0') || LPAD(TO_CHAR(birth_day), 2, '0'), 'yyyyMMdd') <= (#{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を呼び出し、それぞれの実行時間を表示するようにしている。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | 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
サンプルプログラムの実行結果
Spring Bootのメインクラス(DemoApplication.java)を実行した結果、コンソールログに出力される内容は以下の通りで、BETWEEN句を利用した方が、SQLが速くなることが確認できる。
要点まとめ
- SQLで指定した範囲内のデータを取得する際、BETWEEN句を利用できる場合は、BETWEEN句を利用した方が、SQLの実行速度が速くなる。
