-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Slow query executed when executing a delete
PostPosted: Tue Oct 31, 2006 6:28 am 
Newbie

Joined: Tue Oct 31, 2006 5:48 am
Posts: 1
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 between sessionFactory.openSession() and session.close():
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=?


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.