Recently we experienced bad performance when searching for a keyword in a binary file using Hibernate 3.2 and Oracle Text from Oracle 10g.

Here is the case - we have a simple Oracle view which represents a candidate for a job and contains candidate’s CV as a binary file.  Following is the Hibernate mapping for the view:

<hibernate-mapping>

      <class name="Candidate" table="V_CANDIDATE" mutable="false">

            <id name="id" type="long" column="CANDIDATE_ID"/>

            <property name="language" type="string" column="LANG"/>

            <property name="resume" type="blob" column="RESUME"/>

      </class>

</hibernate-mapping>

Here is the Java code we used to search for keywords in the CV's:

Criteria query=getSession().createCriteria(Candidate.class);

query.add(Restrictions.eq("language",language));

query.add(Restrictions.sqlRestriction("contains({alias}.resume,?)>0",keyword,Hibernate.STRING))

query.list();

Here is the SQL statement Hibernate sent to Oracle as a result from the code above:

select * from V_CANDIDATE this_ where this_.LANG=? and contains(this_.resume,?)>0

Unfortunately it took more than 6 minutes to return any results or “no results” at all.

When I get the SQL statement from above that Hibernate generated, populated some values and issued it using Oracle SQL Developer  it executes very fast!!!

select * from V_CANDIDATE this_ where this_.LANG='en' and contains(this_.resume,'{java}')>0

What could be the cause for this big difference in speed?

According to Hibernate documentation it is a best practice to use bind variables as we did. Quote:

"As in JDBC, always replace non-constant values by "?". Never use string manipulation to bind a non-constant value in a query! Even better, consider using named parameters in queries. "

Then I decided to try what will happen if I construct the SQL statement myself not relying on Hibernate and use Hibernate only to send the statement to Oracle. Here is the code:

StringBuffer sqlQuery=new StringBuffer("select * from V_CANDIDATE  this_ where");

 sqlQuery.append(" this_.LANG='"+language+"'");

sqlQuery.append(" and contains(this_.resume,");

sqlQuery.append("'"+keyword+"'");

sqlQuery.append(")>0"); 

SQLQuery query=getSession().createSQLQuery(sqlQuery.toString()).addEntity(Candidate.class);

query.list();

The result was staggering! Now the query run almost as fast as the one issued in Oracle SQL Developer.

Based on this a conclusion can be drawn that whenever we have to search for a keyword in a binary file using Hibernate and Oracle text the best practice is to use literals instead of bind variables