SQL SELECT文を記載する際、「SELECT *」よりは「SELECT (カラム名)」を、「SELECT COUNT(*)」よりは「SELECT COUNT((カラム名))」を利用した方が、SQLの実行速度を速くすることができる。
今回は、select文で「*」を利用した場合とカラム名を利用した場合それぞれで、SQLの実行速度を測定するプログラムを作成してみたので、共有する。
なお、SQL文の性能改善を行う方法については、以下のサイトを参照のこと。
https://sites.google.com/site/orapeform/sql_minaoshi
前提条件
下記記事の実装が完了していること。
Oracle上で@Transactionalアノテーションをネストして利用してみた@Transactionalアノテーションはクラス間でネストして利用することができ、propagation属性の設定値を変えることで、今...
サンプルプログラムの作成
作成したサンプルプログラムの構成は、以下の通り。
なお、上記の赤枠は、前提条件のプログラムから追加・変更したプログラムである。
Mapperインタフェース・Mapper XMLの内容は以下の通りで、select文で「*」を利用した場合とカラム名を利用した場合それぞれで、全データを取得するSQLと、全データ件数を取得する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 | package com.example.demo; import org.apache.ibatis.annotations.Mapper; import java.util.List; @Mapper public interface UserDataMapper { /** * 指定したIDをもつユーザーデータテーブル(user_data)のデータを取得する * @param id ID * @return ユーザーデータテーブル(user_data)の指定したIDのデータ */ UserData findById(Long id); /** * select * によってユーザーデータテーブル(user_data)のデータを全件取得する * @return ユーザーデータテーブル(user_data)のリスト */ List<UserData> findAllByAsta(); /** * select (カラム名) によってユーザーデータテーブル(user_data)のデータを全件取得する * @return ユーザーデータテーブル(user_data)のリスト */ List<UserData> findAllByColumn(); /** * select count(*) によってユーザーデータテーブル(user_data)のデータ件数を取得する * @return ユーザーデータテーブル(user_data)のデータ件数 */ Long countAllByAsta(); /** * select count((カラム名)) によってユーザーデータテーブル(user_data)のデータ件数を取得する * @return ユーザーデータテーブル(user_data)のデータ件数 */ Long countAllByColumn(); } |
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 | <?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="findAllByAsta" resultMap="userDataResultMap"> SELECT * FROM USER_DATA </select> <select id="findAllByColumn" resultMap="userDataResultMap"> SELECT id, name, birth_year, birth_month, birth_day, sex, memo FROM USER_DATA </select> <select id="countAllByAsta" resultType="long"> SELECT COUNT(*) FROM USER_DATA </select> <select id="countAllByColumn" resultType="long"> SELECT COUNT(id) FROM USER_DATA </select> </mapper> |
また、ユーザデータテーブルのエンティティクラスの内容は、以下の通り。
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 | package com.example.demo; import lombok.Data; @Data public class UserData { /** ID */ private long id; /** 名前 */ private String name; /** 生年月日_年 */ private int birthY; /** 生年月日_月 */ private int birthM; /** 生年月日_日 */ private int birthD; /** 性別 */ private String sex; /** メモ */ private String memo; } |
さらに、サービスクラスのサブクラスの内容は以下の通りで、それぞれの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 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | package com.example.demo; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; 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("--- SELECT * によってユーザーデータテーブル(user_data)の" + "データを全件取得するSQLを実行します start. ---"); // 処理前の現在時刻を取得 long startTime = System.currentTimeMillis(); // SELECT * によってユーザーデータテーブル(user_data)のデータを全件取得する List<UserData> userDataList = userDataMapper.findAllByAsta(); // 処理後の現在時刻を取得 long endTime = System.currentTimeMillis(); System.out.println("取得件数 : " + userDataList.size()); System.out.println("処理時間：" + (endTime - startTime) + " ms"); System.out.println("--- SELECT * によってユーザーデータテーブル(user_data)の" + "データを全件取得するSQLを実行します end. ---"); System.out.println(); System.out.println("--- SELECT (カラム名) によってユーザーデータテーブル(user_data)の" + "データを全件取得するSQLを実行します start. ---"); // 処理前の現在時刻を取得 startTime = System.currentTimeMillis(); // SELECT (カラム名) によってユーザーデータテーブル(user_data)のデータを全件取得する userDataList = userDataMapper.findAllByColumn(); // 処理後の現在時刻を取得 endTime = System.currentTimeMillis(); System.out.println("取得件数 : " + userDataList.size()); System.out.println("処理時間：" + (endTime - startTime) + " ms"); System.out.println("--- SELECT (カラム名) によってユーザーデータテーブル(user_data)の" + "データを全件取得するSQLを実行します end. ---"); System.out.println(); System.out.println("--- SELECT count(*) によってユーザーデータテーブル(user_data)の" + "データ件数を取得するSQLを実行します start. ---"); // 処理前の現在時刻を取得 startTime = System.currentTimeMillis(); // SELECT count(*) によってユーザーデータテーブル(user_data)のデータ件数を取得する Long cntUserData = userDataMapper.countAllByAsta(); // 処理後の現在時刻を取得 endTime = System.currentTimeMillis(); System.out.println("取得件数 : " + cntUserData); System.out.println("処理時間：" + (endTime - startTime) + " ms"); System.out.println("--- SELECT count(*) によってユーザーデータテーブル(user_data)の" + "データ件数を取得するSQLを実行します end. ---"); System.out.println(); System.out.println("--- SELECT count((カラム名)) によってユーザーデータテーブル" + "(user_data)のデータ件数を取得するSQLを実行します start. ---"); // 処理前の現在時刻を取得 startTime = System.currentTimeMillis(); // SELECT count((カラム名)) によってユーザーデータテーブル(user_data)のデータ件数を取得する cntUserData = userDataMapper.countAllByColumn(); // 処理後の現在時刻を取得 endTime = System.currentTimeMillis(); System.out.println("取得件数 : " + cntUserData); System.out.println("処理時間：" + (endTime - startTime) + " ms"); System.out.println("--- SELECT count((カラム名)) によってユーザーデータテーブル" + "(user_data)のデータ件数を取得するSQLを実行します end. ---"); } } |
また、サービスクラスの内容は以下の通りで、性能測定のためのサービスクラスのサブクラスのメソッドを2回呼び出している。
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.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; @Service public class DemoService { /** * 性能測定を行うための個別サービス */ @Autowired private DemoServiceSub demoServiceSub; /** * 性能検証を行うためのサービス */ public void verifyPerformance() { System.out.println("com.example.demo.DemoService.verifyPerformance start."); System.out.println(); System.out.println("=== 1回目の性能測定を行います. ==="); demoServiceSub.verifyPerformanceEach(); System.out.println(); System.out.println("=== 2回目の性能測定を行います. ==="); demoServiceSub.verifyPerformanceEach(); System.out.println(); System.out.println("com.example.demo.DemoService.verifyPerformance end."); } } |
さらに、Spring Bootのメインクラスの内容は以下の通りで、サービスクラスのメソッドを呼び出している。
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 | package com.example.demo; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.CommandLineRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication public class DemoApplication implements CommandLineRunner { /** * 性能検証を行うためのサービス */ @Autowired private DemoService demoService; public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } @Override public void run(String... args) { try { // 性能検証を行うためのサービスを呼び出す demoService.verifyPerformance(); } catch (Exception ex) { System.err.println(ex); } } } |
その他のソースコード内容は、以下のサイトを参照のこと。
https://github.com/purin-it/java/tree/master/spring-boot-oracle-performance-select-all/demo
「AOMEI Backupper」は様々な形でバックアップ取得や同期処理が行える便利ツールだったパソコン内のデータを、ファイル/パーティション/ディスク等の様々な単位でバックアップしたり、バックアップ時のスケジュール設定やリアルタイ...
サンプルプログラムの実行結果
サンプルプログラムの実行結果は以下の通りで、select文で「*」よりカラム名を指定した方が、SQLが速くなることが確認できる。
1) 以下を実行し、USER_DATAテーブルに100,000件のデータを追加する。
1 2 3 4 5 6 7 8 9 | begin execute immediate 'TRUNCATE TABLE user_data'; for i in 1..100000 loop INSERT INTO user_data VALUES (i, 'テスト プリン' || TO_MULTI_BYTE(i) , 2012, 1, 20, TO_CHAR(MOD(i, 2) + 1), 'テスト'); end loop; COMMIT; end; / |
実行後、以下のように、レコード数が100,000件になっていることが確認できる。
2) 1)の状態でSpring Bootのメインクラス(DemoApplication.java)を実行した結果、コンソールログに出力される内容は以下の通り。
＜１回目＞
＜２回目＞
要点まとめ
- SQL SELECT文を記載する際、「SELECT *」よりは「SELECT (カラム名)」を、「SELECT COUNT(*)」よりは「SELECT COUNT((カラム名))」を利用した方が、SQLの実行速度を速くすることができる。
