[hibernate-announce] Performance Problem

[Harald Entner]

Hello everyone,

i have a problem with the performance of hibernate. I execute the 
following code during server startup about 5000 times.

public PoTextModule findTextModuleByNameAndLanguage(String name, String 
uid) {
        long st = System.currentTimeMillis();
        Object[] keyValues = {name, uid};
        try {
            List l = getHibernateTemplate().find("from PoTextModule tm" +
                    " where tm.name = ? and tm.language.UID = ?",keyValues);
            return (PoTextModule) (l.size()>0 ? l.get(0) : null);
        } catch (Exception e) {
            logger.error("Could not determine textmodule with name '" + 
name + "' and language '" + uid +"'");
            logger.error(e,e);
            return null;
        }
        finally {
            logger.info("ftmbnal: " + (System.currentTimeMillis()-st) + 
".");
        }
    }

It should return a textmodule with the given name and language. When the 
server starts, the query is pretty fast. But the more queries have been 
executed, the more time it needs to execute.

The startup looks like this

start transaction
    for all modules
       for all textmodules in the modules
          if textmodule is existent 
                if textmodule hasChanged
                     update module
          else
                create textmodule
       // end for all textmodulesmodules
// end for all modules
       
findTextModuleByNameAndLanguage(String name, String uid)  needs 0 to 30 
ms in the beginning, but after a while sometimes 500ms.

What is wrong? The table has no more than 5000 entries, and there is 
almost no difference if its empty or not.

Maybe too many queries in one transaction?

Here is my hbm:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 
3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-cascade="none" default-access="property" 
default-lazy="true" auto-import="true">
  <class table="PoTextModule" 
name="at.workflow.webdesk.po.model.PoTextModule" mutable="true" 
polymorphism="implicit" dynamic-update="false" dynamic-insert="false" 
select-before-update="false" optimistic-lock="version">
    <cache usage="read-write" include="all" />
    <id name="UID" type="string" column="TEXTMODULE_UID" length="32">
      <generator class="uuid.hex" />
    </id>
    <property name="name" unique="false" optimistic-lock="true" 
lazy="false" generated="never">
      <column name="NAME" unique-key="nameAndLanguage" />
    </property>
    <property name="value" length="1000" unique="false" 
optimistic-lock="true" lazy="false" generated="never" />
    <many-to-one cascade="none" foreign-key="FK_TEXTMODULE_LANGUAGE" 
not-null="true" name="language" unique="false" update="true" 
insert="true" optimistic-lock="true" not-found="exception" embed-xml="true">
      <column name="LANGUAGE_UID" unique-key="nameAndLanguage" />
    </many-to-one>
    <many-to-one column="ACTION_UID" cascade="none" 
foreign-key="FK_TEXTMODULE_ACTION" not-null="false" name="action" 
unique="false" update="true" insert="true" optimistic-lock="true" 
not-found="exception" embed-xml="true" />
    <many-to-one column="TEXTMODULE_PARENT_UID" cascade="none" 
foreign-key="FK_TEXTMODULE_TEXTMODULE" not-null="false" name="parent" 
unique="false" update="true" insert="true" optimistic-lock="true" 
not-found="exception" embed-xml="true" />
    <property name="allowUpdateOnVersionChange" unique="false" 
optimistic-lock="true" lazy="false" generated="never" />
    <set inverse="true" lazy="true" name="childs" sort="unsorted" 
mutable="true" optimistic-lock="true" embed-xml="true">
      <key column="TEXTMODULE_PARENT_UID" on-delete="noaction" />
      <one-to-many class="at.workflow.webdesk.po.model.PoTextModule" 
not-found="exception" embed-xml="true" />
    </set>
    <many-to-one column="MODULE_UID" cascade="none" 
foreign-key="FK_TEXTMODULE_MODULE" not-null="false" name="module" 
unique="false" update="true" insert="true" optimistic-lock="true" 
not-found="exception" embed-xml="true" />
  </class>
</hibernate-mapping>

The transaction is managed via spring.

Thanks in advance,

Harald