This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Hibernate: Optimization: id generation & two step insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate: Optimization: id generation & two step insert" Watch "Hibernate: Optimization: id generation & two step insert" New topic
Author

Hibernate: Optimization: id generation & two step insert

Doug MacKenzie
Greenhorn

Joined: Feb 18, 2009
Posts: 13
Hey guys,

Question on optimizing hibernate's SQL generation when inserting into a table using "sequence" generation of the primary key. Basically what I've noticed is that when hibernate generates the SQL for an insert it is doing so in two steps.

1) Select NEXTVAL FOR SQ_MySequence FROM SYSIBM.dummy1
2) INSERT using the value from step 1 as the primary key

Obviously it would be better if hibernate generated a single SQL statement that looked something like this
INSERT INTO MyTable (id, arg1) VALUES (NEXTVAL FOR SQ_MySequence, val1)

Any ideas on how I'd change my mapping to cause this?

Here's my current mapping:

Thanks,
D
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


1) Select NEXTVAL FOR SQ_MySequence FROM SYSIBM.dummy1
2) INSERT using the value from step 1 as the primary key

Obviously it would be better if hibernate generated a single SQL statement that looked something like this
INSERT INTO MyTable (id, arg1) VALUES (NEXTVAL FOR SQ_MySequence, val1)

Does this really need optimisation? The single SQL statement is still two statements, albeit it the result no longer has to go back over the network. Do you have a performance issue in your application with this?

If you do you can't change the behaviour via the mapping. What you will need to do is implement your own dialect and generator classes that do this work for you.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Doug MacKenzie
Greenhorn

Joined: Feb 18, 2009
Posts: 13
Hi Paul,

You're right, it's not a HUGE optimization, I just found hibernate's generated SQL to be "messy" and causes un-nessary code, when it needn't be. I'm sure I'll come across other instances as I continue developing with hibernate so I was curious as to how I could optimize the mapping. Thanks for the solution

D
 
wood burning stoves
 
subject: Hibernate: Optimization: id generation & two step insert
 
Similar Threads
'survey.hibernate_unique_key' doesn't exist"
Hibernate: ORM Mapping "Could not get next sequence value" SQLCODE: -142
Many to Many Mapping: Link table not getting updated
Best way to generate PK in EJB
Cascading non foreign key to Child Table