Hibernate: Optimization: id generation & two step insert

[Doug MacKenzie]

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:

<class name="DBCivicAddress" table="CIVIC_ADDRESS" >
	<id name="id" column="CIVIC_ADDRESS_ID">
		<generator class="sequence">
			<param name="sequence">SQ_CIVIC_ADDRESS</param>
		</generator>
	</id>		
	<property name="lastModified" column="CIVIC_ADDRESS_DTTM" type="timestamp"/>
</class>

Thanks,
D

[Paul Sturrock]

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]

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