aspose file tools*
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
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate: Optimization: id generation & two step insert