Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Eclipselink: batch reading creates a lot of queries
icon3.gif  Eclipselink: batch reading creates a lot of queries [message #1750913] Sun, 01 January 2017 15:03 Go to next message
Pavel No is currently offline Pavel NoFriend
Messages: 45
Registered: May 2016
Member
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")
        private List<Author> authors
        
        //+ setters and getters
    }

    @Entity
    @Table(name = "authors")
    public class Author {
        
        @Id
        @Column(name = "id")
        private Integer id;
        
        @ManyToOne
        @JoinColumn(name = "articleId")
        @BatchFetch(BatchFetchType.IN)
        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?
Re: Eclipselink: batch reading creates a lot of queries [message #1751314 is a reply to message #1750913] Sat, 07 January 2017 20:44 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1275
Registered: July 2009
Senior Member
Cross post answered here: http://stackoverflow.com/questions/41415680/eclipselink-batch-reading-creates-a-lot-of-queries

In essence, the issue is with two layers of batch fetching being involved, causing children to be loaded in before the parent objects are completely built. Looks like a bug in EclipseLink is causing the fetch for a children's parent to query for the data rather than use what is already gathered from the previous query to build it - a minor bug as batch fetching isn't meant for 1:1, and likely forces a read-all query to be sure all values are fetched. This is resolved by using eager in one of the two relationships involved, allowing the parents to be fully built, and by removing the batch fetch on the 1:1 for this query.
Previous Topic:Moxy - return class type for XML element
Next Topic:Erro: Column name is being ignored
Goto Forum:
  


Current Time: Sun Dec 17 13:33:42 GMT 2017

Powered by FUDForum. Page generated in 0.01703 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software