Hibernate Community • View topic - joined-subclass does not allow jdbc batching to happen

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

joined-subclass does not allow jdbc batching to happen

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

mahajan

Post subject: joined-subclass does not allow jdbc batching to happen

Posted: Wed May 13, 2009 7:33 pm

Newbie

Joined: Wed May 13, 2009 7:13 pm
Posts: 3

Our persistence component receives objects over jms and we have to save them to database. The object structure is pretty much a combination of inheritence and containment. We are inserting records with batch size of 100. When we enable order_inserts, we get a good batching for entitites that are in containment. e.g. object containing other objects in lists or collections. However, if an object or entity (in hibernate terms) itself is a joined-subclass (one table per abstract class), then the batching breaks down hitting performance. This is because for each entity insert, there are 3 sqls generated, and whenever a AbstractBatcher prepareBatchStatement sees a different sql, it will run the executeBatch thus wasting the batching concept.

Why cant the ordering of the inserts be done at the org.hibernate.jdbc level classes (which is closer to the database level) such as AbstractBatcher and Batching Batcher. Instead the order of the inserts is done at entity level in the InsertActionSorter().sort(); in the ActionQueue of the org.hibernate.engine package. So even though you are able to batch the entities correctly, you are not able to batch if the entity itself is a combination of 2 or 3 sql inserts due to the object and table hierarchy.

You could have maintaned multiple prepared statements and added to batches of each prepared statement. I understand that this might not work for entities [*especially* not if it is an identity insert], but where ids are pre-assigned, there is no harm maintaining multiple open prepared statements and running individually the addBatch and execute batch on them.

This is causing our persisters to move away from hibernate to a raw jdbc inserts where poc have given 3x performance.

Top

Urmech

Post subject: Re: joined-subclass does not allow jdbc batching to happen

Posted: Thu May 14, 2009 4:44 am

Regular

Joined: Thu Apr 14, 2005 10:39 am
Posts: 115

Hi,

have you tried to change the id generation to sequence?

E. g.

Code:

  @GeneratedValue(... , strategy = GenerationType.SEQUENCE)
    @SequenceGenerator( ... ,allocationSize = 100)

So hibernate don't have to request each Id from the db.
But keep in mind, that this may cause that not every possible id is used. Cause if you have only inserted 50 entries, a new creation of a session-factory cause an increase by the allocationSize, regardless of how many id's where used before. So you should keep allocationSize small.

Greetings Michael

Top

mahajan

Post subject: Re: joined-subclass does not allow jdbc batching to happen

Posted: Thu May 14, 2009 8:50 pm

Newbie

Joined: Wed May 13, 2009 7:13 pm
Posts: 3

Hi,
I think in the sequence of interaction between hibernate and db, id generation is not an issue. There are no hits for id generation. The problem is in the sequence of execution. Lets take a very simple example.

DeliveryOrder extends Order
DeliveryOrder has two attributes DeliveryAddress and BillingAddress.

So in the mapping -

Code:

   <class name="Order" table="AbsOrder" >
      <id name="id" column="id" type="long" />
      <property name="amt" />
      <property name="orderCurrency" column="Currency"/>
      <property name="customerId" column="customer_Id" />
   </class>
   
   <joined-subclass name="DeliveryOrder" extends="Order"  table="DeliveryOrder" select-before-update="false"  >
      <key> <column name="id"  /> </key>
      <property name="deliveryDate" column="Delivery_Date" />
      <property name="deliveryNotes" column="Note" />
      
      <many-to-one name="deliveryAddress" class="Address" column="del_addr_id" cascade="save-update" />
      <many-to-one name="billingAddress" class="Address" column="bill_addr_id" cascade="save-update"  />      
      
   </joined-subclass>

This generates a following sequence of SQLs -

Code:

Hibernate: select max(id) from ADDRESS
Hibernate: insert into ADDRESS (city, country, zip, id) values (?, ?, ?, ?)
Hibernate: insert into ADDRESS (city, country, zip, id) values (?, ?, ?, ?)
---
---
Hibernate: insert into ADDRESS (city, country, zip, id) values (?, ?, ?, ?)

Hibernate: insert into AbsOrder (amt, Currency, customer_Id, id) values (?, ?, ?, ?)
Hibernate: insert into DeliveryOrder (Delivery_Date, Note, del_addr_id, bill_addr_id, id) values (?, ?, ?, ?, ?)
Hibernate: insert into AbsOrder (amt, Currency, customer_Id, id) values (?, ?, ?, ?)
Hibernate: insert into DeliveryOrder (Delivery_Date, Note, del_addr_id, bill_addr_id, id) values (?, ?, ?, ?, ?)
Hibernate: insert into AbsOrder (amt, Currency, customer_Id, id) values (?, ?, ?, ?)
Hibernate: insert into DeliveryOrder (Delivery_Date, Note, del_addr_id, bill_addr_id, id) values (?, ?, ?, ?, ?)
Hibernate: insert into AbsOrder (amt, Currency, customer_Id, id) values (?, ?, ?, ?)
Hibernate: insert into DeliveryOrder (Delivery_Date, Note, del_addr_id, bill_addr_id, id) values (?, ?, ?, ?, ?)
Hibernate: insert into AbsOrder (amt, Currency, customer_Id, id) values (?, ?, ?, ?)
Hibernate: insert into DeliveryOrder (Delivery_Date, Note, del_addr_id, bill_addr_id, id) values (?, ?, ?, ?, ?)
Saving the batch no 1 in 1159

Address inserts have happened earlier, which helped in JDBC batching. However, DeliveryOrder and AbsOrder inserts have got interleaved causing batching to fail in AbstractBatcher. this can be clearly seen from the debug log

Code:

53:07,028 [main] DEBUG hibernate.jdbc.AbstractBatcher - reusing prepared statement
53:07,028 [main] DEBUG org.hibernate.SQL - insert into ADDRESS (city, country, zip, id) values (?, ?, ?, ?)
53:07,028 [main] DEBUG persister.entity.AbstractEntityPersister - Calling batcher from entity
53:07,028 [main] DEBUG hibernate.jdbc.BatchingBatcher - called addToBatch
-----
53:07,043 [main] DEBUG hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
53:07,043 [main] DEBUG org.hibernate.SQL - insert into AbsOrder (amt, Currency, customer_Id, id) values (?, ?, ?, ?)
53:07,043 [main] DEBUG persister.entity.AbstractEntityPersister - Calling batcher from entity
53:07,043 [main] DEBUG hibernate.jdbc.BatchingBatcher - called addToBatch
53:07,043 [main] DEBUG persister.entity.AbstractEntityPersister - Expectation allows batching : true: j 1
53:07,043 [main] DEBUG hibernate.jdbc.BatchingBatcher - Executing batch size: 1
53:07,059 [main] DEBUG hibernate.jdbc.Expectations - success of batch update unknown: 0
53:07,059 [main] DEBUG hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
53:07,059 [main] DEBUG hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
53:07,059 [main] DEBUG org.hibernate.SQL - insert into DeliveryOrder (Delivery_Date, Note, del_addr_id, bill_addr_id, id) values (?, ?, ?, ?, ?)
53:07,059 [main] DEBUG persister.entity.AbstractEntityPersister - Calling batcher from entity
53:07,059 [main] DEBUG hibernate.jdbc.BatchingBatcher - called addToBatch
53:07,059 [main] DEBUG persister.entity.AbstractEntityPersister - Expectation allows batching : true: j 0
53:07,059 [main] DEBUG hibernate.jdbc.BatchingBatcher - Executing batch size: 1
53:07,075 [main] DEBUG hibernate.jdbc.Expectations - success of batch update unknown: 0
53:07,075 [main] DEBUG hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)

As log statements indicate, batching could be done for address but not for AbsOrder and DeliveryOrder. if the prepared statements were tracked by the AbstractBatcher, it would have been able to batch it correctly.

Top

mahajan

Post subject: Re: joined-subclass does not allow jdbc batching to happen

Posted: Sat May 30, 2009 11:31 pm

Newbie

Joined: Wed May 13, 2009 7:13 pm
Posts: 3

Hi, we solved the problem without going away from hibernate. Posting here for benefits of all others who wish to get benefit of the JDBC batching.

The results have been phenomenal. In the Proof of concept, the benefit is 21 seconds reduced to 3 seconds for 5k inserts. for our actual application, the inserts have moved from 21 messages / sec to 330 messages per second.

The idea was to use Oracle insert all statement as follows and use the sql-insert statement in the mapping as follows:

Code:

inser into table absorder (id, col1, col2) values (id, ?,?) table deliveryorder (id, col11, col22) values (id, ?,?) select ? as id from dual; 

and replacing the joined-subclass with a union-subclass definition for deliveryorder and normal class definition for absorder. The inserts fly now.

Top

Matt@Wheatleys

Post subject: Re: joined-subclass does not allow jdbc batching to happen

Posted: Tue Apr 12, 2011 9:40 am

Newbie

Joined: Tue Mar 08, 2011 1:13 pm
Posts: 1

Hi,

We seem to be having a similar issue, I have attempted to try the idea of using sql-inserts in the mapping as suggested without much luck.

Does anyone have any extra info that might be of use to us around implementing this solution?

Thanks.

Top

Page 1 of 1

[ 5 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

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