Hi!
Hibernate version:
2.1.8
Name and version of the database you are using:
PostgreSQL 8.0
Mapping documents:
Code:
<hibernate-mapping>
<class
name="com.test.cm.content.persistence.PropertyValue"
table="cm_property_value"
proxy="com.test.cm.content.persistence.PropertyValue"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="id"
column="id"
type="java.lang.Long"
>
<generator class="native">
</generator>
</id>
<many-to-one
name="property"
class="com.test.cm.content.persistence.Property"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="id_property"
not-null="true"
/>
<many-to-one
name="basicContentData"
class="com.test.cm.content.persistence.BasicContentData"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="id_basic_content_data"
not-null="false"
/>
<joined-subclass
name="com.test.cm.content.persistence.PropertyValueBoolean"
table="cm_property_value_boolean"
dynamic-update="false"
dynamic-insert="false"
proxy="com.test.cm.content.persistence.PropertyValueBoolean"
>
<key
column="id"
/>
<property
name="value"
type="java.lang.Boolean"
update="true"
insert="true"
>
<column
name="value"
sql-type="boolean"
/>
</property>
</joined-subclass>
<joined-subclass
name="com.test.cm.content.persistence.PropertyValueDate"
table="cm_property_value_date"
dynamic-update="false"
dynamic-insert="false"
proxy="com.test.cm.content.persistence.PropertyValueDate"
>
<key
column="id"
/>
<property
name="value"
type="java.sql.Date"
update="true"
insert="true"
>
<column
name="value"
sql-type="date"
/>
</property>
</joined-subclass>
<joined-subclass
name="com.test.cm.content.persistence.PropertyValueRelationship"
table="cm_property_value_relationship"
dynamic-update="false"
dynamic-insert="false"
proxy="com.test.cm.content.persistence.PropertyValueRelationship"
>
<key
column="id"
/>
<many-to-one
name="value"
class="com.test.cm.content.persistence.BasicContentData"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="value"
/>
</joined-subclass>
<joined-subclass
name="com.test.cm.content.persistence.PropertyValueMasterDataObject"
table="cm_property_value_master_data_object"
dynamic-update="false"
dynamic-insert="false"
proxy="com.test.cm.content.persistence.PropertyValueMasterDataObject"
>
<key
column="id"
/>
<many-to-one
name="value"
class="com.test.masterdata.persistence.MasterDataObject"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="value"
/>
</joined-subclass>
<joined-subclass
name="com.test.cm.content.persistence.PropertyValueDouble"
table="cm_property_value_double"
dynamic-update="false"
dynamic-insert="false"
proxy="com.test.cm.content.persistence.PropertyValueDouble"
>
<key
column="id"
/>
<property
name="value"
type="java.lang.Double"
update="true"
insert="true"
>
<column
name="value"
sql-type="double precision"
/>
</property>
</joined-subclass>
<joined-subclass
name="com.test.cm.content.persistence.PropertyValueInteger"
table="cm_property_value_integer"
dynamic-update="false"
dynamic-insert="false"
proxy="com.test.cm.content.persistence.PropertyValueInteger"
>
<key
column="id"
/>
<property
name="value"
type="java.lang.Integer"
update="true"
insert="true"
>
<column
name="value"
sql-type="bigint"
/>
</property>
</joined-subclass>
<joined-subclass
name="com.test.cm.content.persistence.PropertyValueString"
table="cm_property_value_string"
dynamic-update="false"
dynamic-insert="false"
proxy="com.test.cm.content.persistence.PropertyValueString"
>
<key
column="id"
/>
<property
name="value"
type="java.lang.String"
update="true"
insert="true"
>
<column
name="value"
sql-type="text"
/>
</property>
</joined-subclass>
</class>
</hibernate-mapping>
The previous mappings define the PropertyValue classes hierarchy, where every concrete class has a
value property of different types (there is a PropertyValueString where the type of
value is a String, a PropertyValueInteger where the type is an Integer, and so on).
There is another class, BasicContentData, with a List of PropertyValue objects (of different types).
Code:
<hibernate-mapping>
<class
name="com.test.cm.content.persistence.BasicContentData"
table="cm_basic_content_data"
proxy="com.test.cm.content.persistence.BasicContentData"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="id"
column="id"
type="java.lang.Long"
>
<generator class="native">
</generator>
</id>
<many-to-one
name="author"
class="com.test.security.persistence.User"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="id_user"
not-null="true"
/>
<list
name="propertyValues"
lazy="true"
inverse="false"
cascade="all-delete-orphan"
>
<key
column="id_basic_content_data"
/>
<index
column="position"
/>
<one-to-many
class="com.test.cm.content.persistence.PropertyValue"
/>
</list>
</class>
</hibernate-mapping>
Everything is working fine... but painfully slow. I have about 1000 BasicContentData instances, each with 30 PropertyValue objects (of different subtypes). I need to retrieve hundreds of those objects at a time to make a report list, and the query is taking about 45-60 seconds on a P4 machine. I've executed an EXPLAIN on the generated SQL, and the left joins consequence of the PropertyValue hierarchy are really expensive.
I can filter some PropertyValues which don't meet a certain condition, so instead of retrieving full BasicContentData objects, I've tried to perform a query like this and re-construct the BasicContentData objects afterwards.
Code:
select b.id, propertyValue from BasicContentData b left outer join b.propertyValues propertyValue where (propertyValue meets criteria)
But the query is still very slow, taking more than 30 seconds.
I would like to know what can I do to speed up this query, if possible.
Thanks in advance.
Login
FAQ
Search