Generated SQL runs fine, Preparing Statement is SLOW.

jfrankman · **Joined:** Wed Jan 11, 2006 12:59 pm **Posts:** 11

I have three classes in a heiarchy, the Person and Organization classes both inherit from BusinessEntity. In order to get performance for a web search, I have changed inheritance mapping strategies from Table per subclass to Table per class hierarchy. Once I did this, if I copy the generated SQL and run it directly, it works great. However, when it is run inside Hibernate, preparing the statment takes up to thirty seconds.

The HQL is as follows:

String query="select p from BusinessEntity p left join fetch p.entityLocations entityLocation join fetch p.policies policy join fetch entityLocation.location location join fetch entityLocation.locationType where (p.search like '" + lastName + "%') ";

Below is my mapping document. Does Hibernate always have to prepare the statment, or can I configure it to submit the generated SQL directly to the DB ?

Hibernate version: 3.1

Mapping documents:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- 
    Mapping file autogenerated by MyEclipse - Hibernate Tools
-->
<hibernate-mapping package="com.idfbins.domain.busent">
    <class name="BusinessEntity" table="FBPERSCMPY2" schema="CAPROTOTYP" catalog="FBMAS270" discriminator-value="BD">
        <id name="id" type="long">
            <column name="ENTITYID" />
            <generator class="native" />
        </id>
        
        <discriminator column="ENTTYPE" type="string"/>
        
        <property 
           name="notes"
           column="NOTES"
           type="string"
        />
        <property 
           name="search"
           column="SEARCH"
           type="string"
        />
        
      <set name="entityLocations"
         cascade="all-delete-orphan"
         inverse="true"
         lazy="true">
         <key>
            <column name="ENTITYID" not-null="true" length="16"/>
         </key>
         <one-to-many class="EntityLocation"/>
      </set>

      <set name="policies"
         cascade="all-delete-orphan"
         inverse="true"
         lazy="true">
         <key>
            <column name="ENTITYID" not-null="true" length="16"/>
         </key>
         <one-to-many class="Policy"/>
      </set>
        
        <subclass
           name="Organization"
           discriminator-value="C"
           >
           <property 
              name="name"
              column="CMPYNAME"
              type="string"
         />
         <component name="taxId" class="SocialSecurityNumber">
              <property 
                 name="fullNumber"
                 column="SSN"
                 type="string"
            />
         </component>
         
        </subclass>

        <subclass
           name="Person"
           discriminator-value="P"
           >
           <property 
              name="firstName"
              column="FNAME"
              type="string"
         />
           <property 
              name="lastName"
              column="LNAME"
              type="string"
         />
           <property 
              name="middleName"
              column="MNAME"
              type="string"
         />
           <property 
              name="married"
              column="MARITAL"
              type="string"
         />
           <property 
              name="prefix"
              column="PREFIX"
              type="string"
         />
           <property 
              name="suffix"
              column="SUFFIX"
              type="string"
         />
           <property 
              name="gender"
              column="SEX"
              type="string"
         />
           <property 
              name="birthDate"
              column="BIRTHDATE"
              type="date"
         />
         
         <component name="ssn" class="SocialSecurityNumber">
              <property 
                 name="fullNumber"
                 column="SSN"
                 type="string"
            />
         </component>
        </subclass>
        
    </class>
</hibernate-mapping>

Name and version of the database you are using: DB2/400

Code:

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

What makes you say that it's the creation of the PreparedStatement that's so slow? How did you eliminate object-creation as the culprit? Which specific line is it that takes so long to run? It's certainly not the line you posted...

It's probably not making much of a difference for a single execution, but if you're planning on running that query more than once, you really should use parameters instead of string concantenation. If you change your HQL to be a single string with a named or positional parameter, your DBMS will be able to cache that execution plan. If you use a different string every time (as you will with your current HQL), execution plans will be reused much more rarely.

Presumably you're putting in those join fetches because you want to prefetch all those objects so that the web report doesn't get lazy initialization errors. The preferred approach for this is to not use join fetch, but use open session in view, instead. However, this won't affect your overall performance: it just means that all the DB hits happen in one go, instead of spread out or skipped entirely, if possible.

jfrankman · **Joined:** Wed Jan 11, 2006 12:59 pm **Posts:** 11

The preferred approach for this is to not use join fetch, but use open session in view, instead.

The reason I thought the problem occurred in preparing statment is that when I turned debug logging on, the log messages would pause on the preparing statement line and wait for about 30 seconds before printing any new messages. I was able to solve the delay by changing the "join fetch" statements to "left join fetch". I am not sure why this helped, but that alleviated the problem.

I will also ditch the string concatenation and use prepared statements. I will also look into the Open Session in View. Thanks for your help.