• Post Reply Bookmark Topic Watch Topic
  • New Topic

Writing bulk data and improving runtime  RSS feed

 
Michael Oberleitner
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I have a Hibernate project where the caller comes with a large number of data which needs to get stored in a mysql database.
The data model consists of four classes, which are respectively connected through a 1-to-n relationship.

I wrote some easy classes to reproduce the problem:









And this is the DAO class:



And hibernate.cfg.xml


So now when I change the children variable in the main function of the DAO the performance slows down. I realized that Hibernate stores every dataset with a single insert statement.
Hibernate: insert into daughter (age, mid_fk, name) values (?, ?, ?)
Hibernate: insert into granddaughter (age, did_fk, name) values (?, ?, ?)
Hibernate: insert into granddaughter (age, did_fk, name) values (?, ?, ?)
Hibernate: insert into granddaughter (age, did_fk, name) values (?, ?, ?)
...

Instead of doing it like this:

Hibernate: insert into daughter (age, mid_fk, name) values (?, ?, ?)
Hibernate: insert into granddaughter (age, did_fk, name) values (?, ?, ?) (?, ?, ?) (?, ?, ?) (?, ?, ?) ...

So here is the output of the program with different values for the children variable with show_sql == true:
total Milliseconds for 10: 2327
total Milliseconds for 100: 112654

and with show_sql == false:
total Milliseconds for 10: 2059
total Milliseconds for 100: 93645

Is there a better way to write these objects, because in the real world scenario it's not uncommon to have 10000 children which takes several hours to terminate.

Thanks in advance,
Michael
 
Tim Moores
Saloon Keeper
Posts: 3570
77
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Two things you could try come to mind:
  • Commit the transaction frequently, maybe after the data for each child has been written.
  • If the point of this code is just to load data into the DB, is the use of an ORM really necessary? Straight JDBC code might be faster.
  •  
    H Paul
    Ranch Hand
    Posts: 491
    5
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Try and hope it helps:

    1. Consider using hibernate.jdbc.batch_size
    2. Have a look at this link: hibernate-batch-size-confusion
    http://stackoverflow.com/questions/6687422/hibernate-batch-size-confusion

    Or google for same topic: how-to-make-hql-that-will-generate-sql-to-insert-multiple-values-in-one-statemen

    http://stackoverflow.com/questions/17862180/how-to-make-hql-that-will-generate-sql-to-insert-multiple-values-in-one-statemen
     
    It is sorta covered in the JavaRanch Style Guide.
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!