Slow search for Hibernate Search index

Question

That’s a crosspost that I did in the stackoverflow in English. It is difficult to understand (and correct) this problem. I’ll put it here to see if anyone in our community has been through this and knows what might be going on.

What happens is that I have a view on Oracle. That one view it takes a while to execute (~7 seconds), although it returns few records. Return about 5756 lines when I make a simple query like select * from my_view;

So far I have no problem. The problem occurs in my project where I have an entity of Hibernate mapped to this view and also an index mapping of the Hibernate Search so that I can do a fulltext search across the content.

Better show code. This is my mapped entity:

@Entity
@Indexed
@Table(name = "my_view")
public class Person implements Serializable {

    private static final long serialVersionUID = 244555315052436669L;

    @Id
    @Column(name = "id", insertable = false, updatable = false)
    private Long id;

    @Field(store = Store.NO, index = Index.YES, analyze = Analyze.YES)
    @Column(name = "name", insertable = false, updatable = false)
    private String name;

    @Field(store = Store.NO, index = Index.YES, analyze = Analyze.YES)
    @Column(name = "email", insertable = false, updatable = false)
    private String email;

    @Field(store = Store.NO, index = Index.YES, analyze = Analyze.YES)
    @Column(name = "user", insertable = false, updatable = false)
    private String user;

    @Field(store = Store.YES, index = Index.YES, analyze = Analyze.YES)
    @Column(name = "phone", insertable = false, updatable = false)
    private String phone;

    //Getters and Setters ommited
}

Note that there is normal mapping of Hibernate / JPA and Hibernate Search (@Field, for example). So far cool. I think it’s all right. So I created a method to create the index. This is the method:

public void index() throws DAOException {
    FullTextEntityManager fullTextEntityManager = Search.getFullTextEntityManager(this.entityManager);

    try {
        fullTextEntityManager.createIndexer(Person.class)
            .purgeAllOnStart(Boolean.TRUE)
            .optimizeOnFinish(Boolean.TRUE)
            .startAndWait();
    }
    catch (InterruptedException e) {
        logger.error("Error creating index", e);
        throw new DAOException(e);
    }
}

Here too, all right. It creates the index in the right file system. Now comes the problem. The search itself on top of the created index. This search, as it is a fulltext search, should be extremely fast, after all Hibernate Search is built on top of Lucene, which values performance.

When I do a search for terms, it actually goes well (not so well, but it goes), the problem occurs when I search without any term, ie when I want to return me all the records of the index. It takes more than 40 seconds a search without terms! An absurdity of time for an indexed search.

The code of my search:

FullTextEntityManager fullTextEm = Search.getFullTextEntityManager(this.entityManager);
QueryBuilder qb = fullTextEm.getSearchFactory().buildQueryBuilder().forEntity(Person.class).get();
FullTextQuery fullTextQuery = fullTextEm.createFullTextQuery(qb.all().createQuery());
Sort sortField = new Sort(new SortField("name", SortField.STRING));
fullTextQuery.setSort(sortField);
return fullTextQuery.getResultList();

I have been hitting head for some time without success. Any suggestion or tip is welcome!

Answer 1

Problem solved.

What happens is that Hibernate Search, while retrieving the data in the index, uses this data to perform a search on view in the database and retrieve complete data from the mapped entity. When fetching data from the view, the query execution time was slow and this made my application slow.

To solve the problem I had to use the functionality of Projections Hibernate Search. This feature allows me to store the entity information in the index, leaving the index higher, but much faster.

Going straight to the point, what I had to do was change my entity to save the data in the index using the annotation store = Store.YES. My entity was like this:

@Entity
@Indexed
@Table(name = "my_view")
public class Person implements Serializable {

    private static final long serialVersionUID = 244555315052436669L;

    @Id
    @Column(name = "id", insertable = false, updatable = false)
    private Long id;

    @Field(store = Store.YES, index = Index.YES, analyze = Analyze.YES)
    @Column(name = "name", insertable = false, updatable = false)
    private String name;

    @Field(store = Store.YES, index = Index.YES, analyze = Analyze.YES)
    @Column(name = "email", insertable = false, updatable = false)
    private String email;

    @Field(store = Store.YES, index = Index.YES, analyze = Analyze.YES)
    @Column(name = "user", insertable = false, updatable = false)
    private String user;

    @Field(store = Store.YES, index = Index.YES, analyze = Analyze.YES)
    @Column(name = "phone", insertable = false, updatable = false)
    private String phone;

    //Getters and Setters ommited
}

Then, I had to change the way I did the query on top of the index. This change is to specify what I want to use projections. Then my search method in the index was as follows:

FullTextEntityManager fullTextEm = Search.getFullTextEntityManager(this.entityManager);
QueryBuilder qb = fullTextEm.getSearchFactory().buildQueryBuilder().forEntity(Person.class).get();
FullTextQuery fullTextQuery = fullTextEm.createFullTextQuery(qb.all().createQuery());

// Adicionei essa linha para usar projections
fullTextQuery.setProjection("id", "name", "email", "user", "phone");

Sort sortField = new Sort(new SortField("name", SortField.STRING));
fullTextQuery.setSort(sortField);
return fullTextQuery.getResultList();

Now the problem of using Projections is that Hibernate Search returns a list of objects array and not the list with my entity. In other words, Hibernate Search, with Projections, returns a List<Object[]> and without Projections returns List<Person>. So I had to create an auxiliary method to convert the List<Object[]> for List<Person>. The code:

private List<Person> toList(List<Object[]> objPeople) {
    List<Person> lstPeople = new LinkedList<Person>();
    Person Person = null;

    for(Object[] objPerson : objPeople) {
        Person p = new Person();
        p.setId(Long.parseLong(objPerson[0].toString()));
        p.setName(String.valueOf(objPerson[1] == null ? "" : objPerson[1]));
        p.setEmail(String.valueOf(objPerson[2] == null ? "" : objPerson[2]));
        p.setUser(String.valueOf(objPerson[3] == null ? "" : objPerson[3]));
        p.setPhone(String.valueOf(objPerson[4] == null ? "" : objPerson[4]));
        lstPeople.add(p);
    }

    return lstPeople;
}

And just like that! Now it works and very, very fast. The search on top of the keyword without keyword takes any milliseconds.