Hi,

Hibernate makes two SQL statements when used along with a DB generated sequence.

1) Read sequence value
2) Insert the record...

Is it possible to use a dynamic insert...like that can be used in a regular SQL?

insert into tableA (id, a, b) values (seq.nextval, 10, 20)

Any suggestions? I am using Oracle,

Thanks.

Trouble with that is that Hibernate doesn't know the generated pk after the insert.

I think oracle has some kind of "insert ... returning ..." syntax that we could support in future.

i think it is not possible, it may have many undesired impacts.
select nextval is done when calling session.save(obj)

insert is done when flush() (or commit with flushmode=auto) is called.

Thanks Gavin.

Is there any workaround...I generate around 100,000 records and now it essentially makes it 200,000 DB round trips.

Hibernate is not the right tool for mass data operations. I'd recommend the data import tools of the database for this. (In Oracle, I'd usually also tune the transaction settings and REDO logs for such an import.)

Hi Anthony,

Thanks for your reply.

Your reply states that sequence is read when I call session.save() and insert will happen when I call flush/commit.

But from the output it looks like:

select sequence
insert record
select sequence
insert record
select sequence
insert record
select sequence
insert record
select sequence
insert record

If it works the way you have specified it will be a performance boost...is there some special setting I have to take care?

Thanks.

try to temporary set flushmode = never, and call flush before commit.
But i don't think that in your case, it will change something... you'll have n select nextval and at flushtime n inserts...
http://www.hibernate.org/hib_docs/api/n ... tFlushMode

Hi Anthony,

Thanks for your reply.

I have used "Hibernate Synchronizer" and noticed the implementation is something like after each call to save() a commit is performed.

I think this could be the problem.

I will test this hypothesis and post my results.

Thanks for the feedbacks.