This week's book giveaway is in the OCAJP forum.
We're giving away four copies of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) and have Khalid A Mughal & Rolf W Rasmussen on-line!
See this thread for details.
Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate: Optimization: id generation & two step insert

 
Doug MacKenzie
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Doug MacKenzie
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic