This is a crosspost I did in stackoverflow in English. It is difficult to understand (and correct) this problem. I'll post it here to see if anyone in our community has ever been through this and knows what might be going on.
What happens is that I have a view in Oracle . This view takes a while to execute (~ 7 seconds), although it returns few records. Return about 5756 lines when I make a simple query as select * from my_view;
So far I have no problem. The problem occurs in my project where I have a Hibernate entity mapped to that view and also an index mapping of Hibernate Search so I can do a fulltext search across the entire index.
Better to show the 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 (% with%, for example). So far legal. I think it's all right. So I created a method to create the index. The method is this:
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 correct file system. Now the problem comes. The search itself on the created index. This search, because 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, until it goes well (not so well, but it goes), the problem occurs when I search without any terms, that is, when I want to return all records of the index. It takes more than 40 seconds a search without terms! An absurd time for an indexed search.
The code for my search without terms:
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've been beating head for some time without success. Any suggestions or tips are welcome!