Fetch entity relations using native sql query in JPA repository method

I have a repository method to fetch EmailSubscriptions

    @Query("SELECT es FROM EmailSubscription es "
        + "JOIN FETCH es.subscriber s "
        + "WHERE es.id > :batchStartId "
        + "AND es.subscriptionTypeId = :typeId "
        + "AND es.active = :active "
        + "ORDER BY es.id ASC")
    List<EmailSubscription> findByIdGreaterThanAndSubscriptionTypeIdAndActive(
        @Param("batchStartId") long batchStartId,
        @Param("typeId") Integer typeId,
        @Param("active") Boolean active,
        Pageable pageable);

Here is how EmailSubscriptions is defined.

public class EmailSubscription {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(updatable = false, nullable = false)
    private Long id;

    @Column(name = "type_id")
    private Integer subscriptionTypeId;
    private Boolean active;
    @Pattern(regexp = LOCALE_VALIDATION_REGEX, message = "invalid code")
    private String locale;
    private LocalDateTime lastSentTimestamp;
    private Integer lastSentSequence;
    private Long lastUpdatePersonId;
    private LocalDateTime lastUpdateTimestamp;
    private LocalDateTime creationTimestamp;

    @ManyToOne(targetEntity = Subscriber.class, fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "subscriber_id", referencedColumnName = "id")
    private Subscriber subscriber;
}

Unfortunately this query is very expensive for a batch of 10k records (10+ seconds). So I wrote a native sql query which is under 1 second to fetch 10k records.

            @Query(value = "SELECT es.* FROM subscriptions.email_subscriptions es "
                + "IGNORE INDEX (PRIMARY) "
                + "JOIN subscriptions.subscribers s ON es.subscriber_id = s.id "
                + "WHERE es.type_id = :typeId "
                + "AND es.active = :active "
                + "AND es.id > :batchStartId "
                + "ORDER BY es.id ", nativeQuery = true)

But unfortunately this query only fetches EmailSubscriptions without its Subscriber relation. How can I fetch Subscriber within EmailSusbcription using native sql query

Solution

Instead of working around JPA and the features it gives, embrace it. Also understand JDBC and the default settings.

In this case you need to understand fetch sizes. Most JDBC drivers have a fetch size of 10. Which means it will retrieve 10 rows, process them and retrieve the next 10. Each call is IO/network access which is slow.

Increase the fetchsize for this query, you can use an @QueryHint with Spring Data for that.

@Query("SELECT es FROM com.cargurus.emailsubscriptions.entity.EmailSubscription es "
        + "JOIN FETCH es.subscriber s "
        + "WHERE es.id > :batchStartId "
        + "AND es.subscriptionTypeId = :typeId "
        + "AND es.active = :active "
        + "ORDER BY es.id ASC")
@QueryHints(@QueryHint(name = "org.hibernate.fetchSize", value = "1000"))
List<EmailSubscription> findByIdGreaterThanAndSubscriptionTypeIdAndActive(
        @Param("batchStartId") long batchStartId,
        @Param("typeId") Integer typeId,
        @Param("active") Boolean active,
        Pageable pageable);

Rewriting this with SQL won't help as you probably endup with the same query that Hibernate generates but you will have to do the mapping yourself (leading to the same slowness).