Defining Custom Select Queries when using Search

rehtonAesoohC · **Joined:** Thu Jul 01, 2010 12:45 pm **Posts:** 11

We are using Hibernate Search extensively in a new application, but I am running into performance issues with querying.

The issue stems from the fact that I can't control the SQL that is generated from and follows a Hibernate Search (Lucene) query.

Consider the following:

Code:

FullTextSession fullTextSession = Search
            .getFullTextSession(this.sessionFactory.getCurrentSession());
      MultiFieldQueryParser parser = new MultiFieldQueryParser(
            Version.LUCENE_29, fields, fullTextSession.getSearchFactory()
                  .getAnalyzer(MasterIdentity.class));
      List<MasterIdentity> list = null;
      try {
         org.apache.lucene.search.Query query = parser.parse(searchQuery);
         org.hibernate.search.FullTextQuery fullTextQuery = fullTextSession
               .createFullTextQuery(query, MasterIdentity.class)
               .setMaxResults(results).setFirstResult(index);
         org.apache.lucene.search.Sort sorter = new Sort();

         if (sortFields != null && sortFields.length > 0) {
            sorter.setSort(sortFields);
         } else {
            SortField sf = new SortField(searchQuery, SortField.STRING);
            sorter.setSort(sf);
         }

         fullTextQuery.setSort(sorter);
         list = fullTextQuery.list();
      } catch (ParseException e) {
         e.printStackTrace();
      }

At "fullTextQuery.list();", Hibernate Search executes the Lucene query, is returned a stub result set, and then executes a standard "select this_.active_bid as active1_1_7_, this_.ATP as ATP1_7_, this_.create_date as create3_1_7_....."

The resulting query takes quite a while and includes several left outer joins. Is there a way I get in between Lucene and Hibernate and have my query constructed the way I want it constructed after the Lucene search is completed, but before the stub Hibernate object is populated?

Thanks so much.

hardy.ferentschik · **Posted:** Fri Oct 22, 2010 9:56 am

You cannot control the query which Search will execute to load the entities. However, it tries to be as efficient as possible. Maybe your Hibernate mapping can be optimized? An alternative approach could be projection. Maybe it would help if you post your domain model.

--Hardy

sanne.grinovero · **Posted:** Mon Oct 25, 2010 2:52 am

Quote:

The resulting query takes quite a while and includes several left outer joins.

From that I guess you're using EAGER loading quite extensively, performance can be improved to a very high extend by replacing eager loading with lazy loading and enabling second level caches to remove the N+1 problem: JDBC restultsets transferred from database are much smaller, less data to process, less work for the garbage collector.
The benefit I like most however is the fact that "query optimization" depends on business logic, and you can avoid that to some extent which improves maintainability.

rehtonAesoohC · **Joined:** Thu Jul 01, 2010 12:45 pm **Posts:** 11

Thanks for the replies!

Unfortunately we have to use Eager loading, because we are loading every collection and sending it to our front-end GUI in a JSON array. Every database object has to be completely populated before we send it to the client.

I was able to get around the issue by using

Code:

@Fetch(value = FetchMode.SELECT)

(and SUBSELECT) to eliminate the outer joins, which improved performance by an order of magnitude, but the jury is still out as to whether or not this will scale efficiently.

Thanks again!

sanne.grinovero · **Posted:** Mon Nov 01, 2010 6:54 pm

I'm glad it helped. You can improve more on it enabling caches, even if it's eager, when using selected and subselect you're going to benefit from it.
Regarding scaling efficiently, just consider that with plain JDBC you would have to perform the same queries: just adapting and splitting these queries in (sub)select statements would be a lot of work. That's why I'm recommending to enable second level caches, at least for those often repeated objects.

BTW I see the "initialize all and encode to JSON quite" practice quite often; but usually this doesn't *need* to load all eagerly, just make sure the session is still open during JSON encoding.

qtxo · **Joined:** Tue Sep 08, 2009 7:36 am **Posts:** 1

If your view requires access to complex object graphs in JSON why not keep it pre-generated in a especial entity like SearchEntity with fields created ad hoc for Hibernate Search anotations.

This way you can decouple your search from your domain objects and optimize both separately, increasing performance by orders of magnitude.

Of course, you will have to keep this table consistent with the relational model, or choose the "eventually consistent" paradigm (choosing which data must be always consistent in the JSON, in contrast to visit counters, ratings, etc..).