Hibernate oracle sequence

MirekSz · **Joined:** Thu Sep 10, 2009 5:43 am **Posts:** 15

Hello
I have a question aboout hibernate and oracle sequence.
I doing some bussiness method which adds about 100.000 records to db. But 1/3 of the time takes queries :
select S_ADDRESS.nextval from dual

How to resolve this problem

Best regards

shynate26 · **Posted:** Mon Aug 09, 2010 6:54 am

Hi mireksz,

Please post your mapping file here. It depends on how you have made the id element's generator class.

<id
name="identifier"
column="identifier"
type="long">
<generator class="sequence">
<param name="sequence">$sequence_name</param>
</generator>
</id>

Hope this helps.

MirekSz · **Joined:** Thu Sep 10, 2009 5:43 am **Posts:** 15

In my mappings I have

Code:

        <id name="idAddress">
            <column name="`ID_ADDRESS`"/>
            <generator class="sequence">
                <param name="sequence">S_ADDRESS</param>
            </generator>
        </id>

shynate26 · **Posted:** Mon Aug 09, 2010 7:17 am

Code:

<property name="show_sql">true</property>
<property name="format_sql">true</property>
<property name="use_sql_comments">true</property>

now have a look at the log , after adding these statements in cfg.xml file. Each time a new object is added , a query is fired.

Please get back here to know what happened.

MirekSz · **Joined:** Thu Sep 10, 2009 5:43 am **Posts:** 15

Code:

select S_ADDRESS.nextval from dual
Hibernate: /* insert Address */ insert into NEXT_TEST."ADDRESS" ("VERSION", "STREET", "HOUSE_NUMBER", "POST_CODE", "POST", "LOCALITY", "FLAT_NUMBER", "COUNTY", "COMMUNE", "CREATE_DATE", "KLU_UNIQ", "ID_ADDRESS_TYPE", "ID_CONTACT", "ID_COUNTRY", "ID_PROVINCE", "ID_ADDRESS") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

select S_ADDRESS.nextval from dual
Hibernate: /* insert Address */ insert into NEXT_TEST."ADDRESS" ("VERSION", "STREET", "HOUSE_NUMBER", "POST_CODE", "POST", "LOCALITY", "FLAT_NUMBER", "COUNTY", "COMMUNE", "CREATE_DATE", "KLU_UNIQ", "ID_ADDRESS_TYPE", "ID_CONTACT", "ID_COUNTRY", "ID_PROVINCE", "ID_ADDRESS") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)


select S_ADDRESS.nextval from dual
Hibernate: /* insert Address */ insert into NEXT_TEST."ADDRESS" ("VERSION", "STREET", "HOUSE_NUMBER", "POST_CODE", "POST", "LOCALITY", "FLAT_NUMBER", "COUNTY", "COMMUNE", "CREATE_DATE", "KLU_UNIQ", "ID_ADDRESS_TYPE", "ID_CONTACT", "ID_COUNTRY", "ID_PROVINCE", "ID_ADDRESS") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

This inserts are in one transaction.
This select S_ADDRESS.nextval from dual takes 1/3 time of this task

shynate26 · **Posted:** Mon Aug 09, 2010 8:07 am

Each time the object is added, during insert it looks for the sequence value. Is that a problem inserting 100 records at a time ?

MirekSz · **Joined:** Thu Sep 10, 2009 5:43 am **Posts:** 15

When I add 100.000 records the hibernate generates
100.000 select (select S_ADDRESS.nextval from dual) Can I cache this sequence or told hibernate to take for example 100 per select

shynate26 · **Posted:** Mon Aug 09, 2010 8:35 am

Here every time the record is inserted, it needs to hit the database to acquire the identifier value. So there is no meaning in caching this query.

Also share your business scenario when 100 records are inserted.