Eclipselink: batch reading creates a lot of queries

Question

I use eclipselink 2.6.4 and I have the following entities

@Entity
@Table(name = "articles")
public class Article {

    @Id
    @Column(name = "id")
    private Integer id;

    @Column(name = "title")
    private String title;

    @OneToMany(fetch = FetchType.EAGER,mappedBy = "article")
    @BatchFetch(BatchFetchType.IN)
    private List<Author> authors

    //+ setters and getters
}

@Entity
@Table(name = "authors")
public class Author {

    @Id
    @Column(name = "id")
    private Integer id;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "articleId")
    private Article article;

    @Column(name = "surname")
    private String surname;

    //+setters and getters
}

And this is the code I use to read all articles with their authors:

String queryString="SELECT e FROM Article e";
Query query = em.createQuery(queryString);
query.setHint("eclipselink.batch.type", "IN");
query.setHint("eclipselink.batch", "e.authors");
query.setFirstResult(position);
query.setMaxResults(amount);
List<Article> items=query.getResultList();

In DB I have 3 articles and every article has two authors. And these are the queries eclipse link executes:

SELECT id AS a1, title AS a2 FROM articles LIMIT ? OFFSET ? bind => [2 parameters bound]
SELECT id, surname, articleId FROM authors WHERE (articleId IN (?,?,?)) bind => [3 parameters bound]
SELECT id, title FROM articles WHERE (id IN (?,?)) bind => [2 parameters bound]
SELECT id, surname, articleId FROM authors WHERE (articleId = ?) bind => [1 parameter bound]
SELECT id, surname, articleId FROM authors WHERE (articleId = ?) bind => [1 parameter bound]

Why so many queries? I expect only two queries. What is my mistake?

EDIT
I did two more tests:

I only used annotation @BatchFetch(BatchFetchType.IN) in Article class on field authors (without adding hints to query)
I didn't use annotation @BatchFetch(BatchFetchType.IN) but used two hints on query:

String queryString="SELECT e FROM Article e"; Query query = em.createQuery(queryString); query.setHint("eclipselink.batch.type", "IN"); query.setHint("eclipselink.batch", "e.authors"); query.setFirstResult(0); query.setMaxResults(10); List items=query.getResultList();

The data in table articles:

| id | title    |
-----------------
| 1  | article1 |
| 2  | article2 |
| 3  | article3 |

The data in table authors:

| id | articleId |  surname  |
------------------------------
| 1  |  1        |  Author1  |
| 2  |  1        |  Author2  |
| 3  |  2        |  Author3  |
| 4  |  2        |  Author4  |
| 5  |  3        |  Author5  |
| 6  |  3        |  Author6  |

In each test 6 queries are executed:

SELECT id AS a1, title AS a2 FROM articles LIMIT ? OFFSET ? bind => [2 parameters bound]
SELECT id, surname, articleId FROM authors WHERE (articleId IN (?,?,?)) bind => [3 parameters bound]
SELECT id, title FROM articles WHERE (id = ?) bind => [1 parameter bound]
SELECT id, surname, articleId FROM authors WHERE (articleId = ?) bind => [1 parameter bound]
SELECT id, title FROM articles WHERE (id = ?) bind => [1 parameter bound]
SELECT id, surname, articleId FROM authors WHERE (articleId = ?) bind => [1 parameter bound]

score 3 · Accepted Answer · edited Feb 03 '17 at 11:24

There is two ways how we can set batch fetch.

Over annotation @BatchFetch(BatchFetchType.IN)
Over query hints query.setHint(QueryHints.BATCH, column); query.setHint(QueryHints.BATCH_TYPE, BatchFetchType.IN);

In your case I see that you added annotation in Author table but query with hints is done on Article table. I don't know whole logic behind this but I suggest:

@Entity
@Table(name = "articles")
public class Article {

    @Id
    @Column(name = "id")
    private Integer id;

    @Column(name = "title")
    private String title;

    @OneToMany(mappedBy = "article", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @BatchFetch(BatchFetchType.IN)
    private List<Author> authors

    //+ setters and getters
} 

@Entity
@Table(name = "authors")
public class Author {

    @Id
    @Column(name = "id")
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "articleId")
    private Article article;

    @Column(name = "surname")
    private String surname;

    //+setters and getters
}

or

do not use that annotation just only query with hints:

String queryString="SELECT e FROM Article e";
Query query = em.createQuery(queryString);
query.setHint("eclipselink.batch.type", "IN");
query.setHint("eclipselink.batch", "e.authors");
query.setFirstResult(position);
query.setMaxResults(amount);
List<Article> items=query.getResultList();

One more thing: From the JPA 2.0 spec, the defaults are like so:

OneToMany: LAZY
ManyToOne: EAGER
ManyToMany: LAZY
OneToOne: EAGER

Eclipse link uses the same:

OneToMany: LAZY
ManyToOne: EAGER
ManyToMany: LAZY
OneToOne: EAGER

@OneToMany must be (fetch = FetchType.EAGER) and @ManyToOne must be (fetch = FetchType.LAZY).

For more information about `@BatchFetch vs @JoinFetch` http://java-persistence-performance.blogspot.co.uk/2010/08/batch-fetching-optimizing-object-graph.html and query optimization http://vard-lokkur.blogspot.co.uk/2011/05/eclipselink-jpa-queries-optimization.html — Saulius Next, Jan 04 '17 at 08:12
I tried two tests: 1) I used only annotation `@BatchFetch(BatchFetchType.IN)` on authors 2) I used only hints. Result is the same - 6 queries. — Pavel_K, Jan 04 '17 at 14:50
Please can you update your question with latest changes and results. Now it is hard to say what's going on. — Saulius Next, Jan 04 '17 at 14:54
FetchType.`EAGER` can you remove or change to `LAZY` (`LAZY` = fetch when needed `EAGER` = fetch immediately) — Saulius Next, Jan 04 '17 at 15:25
The problem was solved. `@OneToMany` must be `(fetch = FetchType.EAGER)` and `@ManyToOne` must be `(fetch = FetchType.LAZY)`. This case I have only two queries although article in Author class is not null. Make it as answer and I will accept it as you gave me the hint. — Pavel_K, Jan 04 '17 at 15:39
Please, add the information I wrote in my previous comment to your answer about LAZY/EAGER as it is the key point. — Pavel_K, Jan 04 '17 at 16:02

score 2 · Answer 2 · answered Jan 04 '17 at 00:11

2

The first 2 queries are as expected based on the JPQL and the batchFetch over a 1:M. The 3rd follows from the @BatchFetch annotation and seems correct, though I do not quite understand why you would use batchfetch on what is essentially a OneToOne instead of using something like @JoinFetch: I don't see much benefit in doing this in two queries.

This looks like a bug in EclipseLink when using the BatchFetch on an eager, OneToOne type mapping involved in a circular relationship - BatchFetch is only meant for queries over collection types, and likely forces a database read instead of using the cache. Options are:

Make one side lazy so that all Article instances will be fully
built in memory before EclipseLink needs to build the Author.article relationships.
Remove the @BatchFetch(BatchFetchType.IN) on the ManyToOne relationship. Use either @FetchJoin, or a query hint to specify BatchFetch when needed in other queries: Neither options are needed for this query.

answered Jan 04 '17 at 00:11

Chris

20,138
2
29
43

Hi. I use batch because of pagination. See my question http://stackoverflow.com/questions/41414874/jpa-onetomanyfetch-fetchtype-eager-pagination-and-duplicates/41454275#41454275 and you answer. – Pavel_K Jan 04 '17 at 07:15
My answer was don't use fetch joins for a collection. Joins are fine for OneToOne and ManyToOne relationships as they don't change the number of rows returned. Batch fetching could be useful on a 1:1, but might not be your best choice here. – Chris Jan 04 '17 at 07:20
Besides, anyway, if there are two options - join-fetch and batch I want to be able use both of them. – Pavel_K Jan 04 '17 at 07:32
You misread my answer as I suggested the solution you worked out in the accepted answer. The problem is due to the circular relationship and your eager fetching everything; your setup causes EL to start building articles starting with A1, then all authors (B1, B2..). In the process of building B2, it needs to build A2 which hasn't yet been built, forcing the query. Switching one of the relationships to be lazy would resolve this, as all objects will be in the cache when it is needed - this is what you went with – Chris Jan 04 '17 at 18:54
The other option was to use fetch-join on the 1:1 relationship. This too would have worked, though would force an unneccessary table join in the query to bring in authors. This would cause Authors to be built up, and when it needs to build the yet unbuilt Articles, the info would be included in the row already so it wouldn't need an extra query. As mentioned, this data is already there, it is just that the 1:1 batch mechanism doesn't look for it - and should be filed as a bug. – Chris Jan 04 '17 at 18:57
Thank you very much for detailed explanation and professional advices. – Pavel_K Jan 04 '17 at 19:07

score 0 · Answer 3 · edited May 23 '17 at 12:16

0

According to the documentation for @BatchFetch

When using BatchFetchType=IN, EclipseLink selects only objects not already in the cache. This method may work better with cursors or pagination, or in situations in which you cannot use a JOIN. On some databases, this may only work for singleton IDs.

So I guess multiple Selects are generated because there is no objects in the Cache. What you try is to run the Same Query with Hot Cache as well.

Furthermore according to you can have look at this SO Question as well.

edited May 23 '17 at 12:16

Community

1
1

answered Jan 03 '17 at 16:36

shazin

21,379
3
54
71

There is no logic, because after the second query these objects are already in cache. – Pavel_K Jan 03 '17 at 17:22

Eclipselink: batch reading creates a lot of queries

3 Answers3