Hibernateのコンストラクタ式でN+1 SELECT問題

Hibernateでコンストラクタ式を使う際、エンティティの参照ごとコンストラクタに渡すとN+1 SELECT問題が起きます。

JPAでは、コンストラクタ式を使うことでJPA Entity以外の型に結果を入れることができます。集計関数の結果をJava Beanに入れたりします。

このコンストラクタ式について、Hibernateではエンティティの参照ごとコンストラクタに渡すとN+1 SELECT問題が起きるようです(StackOverflow参照)。

Quarkusで再現させてみます(ちなみに実務ではSpring Data JPA / Hibernateでハマりました)。Quarkusサンプル集のhibernate-orm-quickstartに、以下のようなJava Beanを追加します。

public class FruitInfo {
  final Fruit fruit;
  final Date date;
  public FruitInfo(Fruit fruit, Date date) {
    this.fruit = fruit;
    this.date = date;
  }
  ...
}

JAX-RS リソースクラスに、問題となるコードを追加します。

@GET
@Path("infos/n-plus-1-selects")
public List<FruitInfo> getInfos1() {
  return entityManager
    .createQuery(
       " SELECT new org.acme.hibernate.orm.FruitInfo(f, current_date()) "
       + " FROM Fruit f",
       FruitInfo.class)
         .getResultList();
}

このAPIをHTTP GETしたときのログです(Hibernate統計情報を出しています)。

Hibernate:
    select
        fruit0_.id as col_0_0_,
        current_date as col_1_0_
    from
        known_fruits fruit0_
Hibernate:
    select
        fruit0_.id as id1_0_0_,
        fruit0_.name as name2_0_0_
    from
        known_fruits fruit0_
    where
        fruit0_.id=?
Hibernate:
    select
        fruit0_.id as id1_0_0_,
        fruit0_.name as name2_0_0_
    from
        known_fruits fruit0_
    where
        fruit0_.id=?
Hibernate:
    select
        fruit0_.id as id1_0_0_,
        fruit0_.name as name2_0_0_
    from
        known_fruits fruit0_
    where
        fruit0_.id=?
2021-01-04 17:46:39,540 INFO  [org.hib.eng.int.StatisticalLoggingSessionEventListener] (executor-thread-1) Session Metrics {
    12834603 nanoseconds spent acquiring 1 JDBC connections;
    27925 nanoseconds spent releasing 1 JDBC connections;
    18157874 nanoseconds spent preparing 4 JDBC statements;
    15507634 nanoseconds spent executing 4 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    2703771 nanoseconds spent performing 3 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    999380 nanoseconds spent performing 3 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

コンストラクタ式をやめるとSELECTが一回になります。

@GET
@Path("infos/workaround")
public List<FruitInfo> getInfos2() {
  return entityManager
           .createQuery(
             " SELECT f, current_date() "
             + " FROM Fruit f", Tuple.class)
               .getResultList().stream()
                  .map(tuple ->
                    new FruitInfo(
                      tuple.get(0, Fruit.class),
                      tuple.get(1, Date.class)))
                  .collect(Collectors.toList());
}

コンストラクタ式を使わない場合のログです。

Hibernate:
    select
        fruit0_.id as col_0_0_,
        current_date as col_1_0_,
        fruit0_.id as id1_0_,
        fruit0_.name as name2_0_
    from
        known_fruits fruit0_
2021-01-04 17:46:48,628 INFO  [org.hib.eng.int.StatisticalLoggingSessionEventListener] (executor-thread-1) Session Metrics {
    56009 nanoseconds spent acquiring 1 JDBC connections;
    20409 nanoseconds spent releasing 1 JDBC connections;
    130352 nanoseconds spent preparing 1 JDBC statements;
    2514127 nanoseconds spent executing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    442439 nanoseconds spent performing 3 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です