Hi,
I have a program which stores 2 types of orders: RetailOrder and SupplierOrder. They both inherit from the abstract class Order. Order has a one-to-many link to an OrderLine which is an abstract class of RetailOrderLine and SupplierOrderLine. Now when I delete an order I want all the orderlines to be removed so cascade is set to "all-delete-orphan". The code works fine however it is very slow. This is due to the problem that all tables have at least more than 400 000 records. There is an index on the order_id field so that`s not the problem. If I enable "show_sql" I see the query as below. The problem is the union. I can make the query much faster if on supplier_order_line and retail_order_line I add the extra condition that order_id must equal "?". However this is generated code by hibernate which I can`t control. One extra thing to notice is that on schema generation with hibernate tool, no Foreign Key is generated on order_id so I added it manually. Does anybode see what I am doing wrong or how I can speed this up.
Thanks in advance for replying,
Tim
Hibernate version:
3.1.3
Mapping documents:
Code:
<class name="Order" abstract="true">
<id name="id" column="order_id">
<generator class="increment"/>
</id>
<set name="lines" inverse="true" cascade="all-delete-orphan">
<key column="order_id"/>
<one-to-many class="OrderLine"/>
</set>
...
<union-subclass name="RetailOrder">
<property name="customer"/>
</union-subclass>
<union-subclass name="SupplierOrder">
...
</union-subclass>
</class>
<class name="OrderLine" abstract="true">
<id name="id" column="order_line_id">
<generator class="increment"/>
</id>
<many-to-one name="order" column="order_id"/>
<union-subclass name="RetailOrderLine">
...
</union-subclass>
<union-subclass name="SupplierOrderLine">
...
</union-subclass>
</class>
Code:
List list = fSession.createQuery("from RetailOrder r where r.customer = :customer").setString("customer","Alice").list();
for (Iterator iterator = list.iterator(); iterator.hasNext();) {
fSession.delete(iterator.next());
}
Name and version of the database you are using:MySQL 4.1.21
The generated SQL (show_sql=true):Code:
select lines0_.order_id as order38_1_, lines0_.order_line_id as order1_1_, lines0_.order_line_id as order1_17_0_, ... lines0_.clazz_ as clazz_0_ from ( select order_line_id, reference, 1 as clazz_ from retailorderline union select order_line_id, reference, 2 as clazz_ from supplierorderline ) lines0_ where lines0_.order_id=?
Login
FAQ
Search