Hibernate version:
2.1.6
Database:
Oracle 9i

Hi,
In our program we have to insert many data (arounf 1 milion of rows).
In first attempt I've used pure Hibernate code.

Map parsedLine = (Map) iterator.next();
if (load) {
loadingStrategy_.load(parsedLine);
if ((lineNumber % 1000) == 0) {
SessionTxManager.commitTxExt(true); // here I make session.clear
SessionTxManager.openSessionStartTx();


Where in "load" method I perform Hibernate save.
10000 rows was inserted in 67sec.

After I've used JDBC with prepared statment ,where commit was executed after 10000 inserts and inserting 10 000 rows took only 9-10sek.

Any suggestions ?!



}
}

http://blog.hibernate.org/cgi-bin/blosx ... batch.html

this has been discussed many many times, there is a search engine in the forum.

I've read this post and I make "clear" but still it few times slower compared to JDBC

is Hibernate JDBC? no
so don't compare it to jdbc.

ORM is not done for massive update/insert, you can use it to do it but it is not the first goal of this kind of tools.

Next you can compare jdbc with sqlloader....

Nevertheless, my tests show no significant difference in performance between Hibernate and direct JDBC when the instructions in that blog entry are followed, in a fair test.

Have you used Oracle for this test? I think the problem is in network communication and the problem is that when I use JDBC prepared statement I can commit after 10 000 insert when I use Hibernate I have to commit after 100 (otherwise Hibernate slow down because cache is not cleared) :(

I ve found in Oracle log strange thing (I hope that I properly understand log), Hibernate prepare statment does not work because is interapted by Select hibernate_sequence.nextval from dual. Am I right?

PARSING IN CURSOR #2 len=43 dep=0 uid=21 oct=3 lid=21 tim=1072462779309174 hv=2476267719 ad='
1761118'
select hibernate_sequence.nextval from dual
END OF STMT
PARSE #2:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1072462779309166
EXEC #2:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1072462779309341
FETCH #2:c=0,e=141,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1072462779310949
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SEQUENCE '
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
=====================
PARSING IN CURSOR #2 len=126 dep=0 uid=21 oct=2 lid=21 tim=1072462779314861 hv=3931213320 ad=
'1737688'
insert into EXCHANGE_RATE (CODE, VALID_FROM, VALID_TILL, EXCHANGE_RATE, NEW, EXCHANGE_RATE_ID
) values (:1, :2, :3, :4, :5, :6)
END OF STMT
PARSE #2:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1072462779314853
EXEC #2:c=0,e=200,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=4,tim=1072462779315190
=====================
PARSING IN CURSOR #2 len=43 dep=0 uid=21 oct=3 lid=21 tim=1072462779318905 hv=2476267719 ad='
1761118'
select hibernate_sequence.nextval from dual
END OF STMT
PARSE #2:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1072462779318898
EXEC #2:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1072462779319068
FETCH #2:c=0,e=93,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1072462779320638
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SEQUENCE '
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
=====================
PARSING IN CURSOR #2 len=126 dep=0 uid=21 oct=2 lid=21 tim=1072462779323941 hv=3931213320 ad=
'1737688'
insert into EXCHANGE_RATE (CODE, VALID_FROM, VALID_TILL, EXCHANGE_RATE, NEW, EXCHANGE_RATE_ID
) values (:1, :2, :3, :4, :5, :6)
END OF STMT
PARSE #2:c=0,e=67,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1072462779323933
EXEC #2:c=0,e=238,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=4,tim=1072462779324309

be carefull with your connection pooling!!!
this has nothing to do with hibernate, it seems to be a connection test
and if it is what i think, it is marked in the doc that this parameter is not to use....

let me imagine... you're using dbcp or c3p0 with a deprecated parameter

You are right after starting application I've got message:

> INFO (DriverManagerConnectionProvider.java:42) 19.10.2004 21:13:50 - Using Hibernate built-in connection pool (not for production use!)

We use Hibernate in standalone program an we do not need pooling, what should be changed - how to switch off pooling ?

you need connection provider, use c3p0 with max pool = 1

Hi you are right
I have such message:

19.10.2004 21:22:24 - Using Hibernate built-in connection pool (not for production use!)

I've set following parameter (we have standalnone application and we do not need pooling):
hibernate.connection.pool_size=0

Is it enought or should I change more things ?

Regards

see previous message

For sure I will check it tomorow.

Regards
Michal

I've checked this and I see improvement in performace, but I after analyzing Oracle trace I've discovered that following line takes a lot of time:
select hibernate_sequence.nextval from dual

I've changed my "id" column in mapping to "assigned" :


<id
name="id"
column="EXCHANGE_RATE_ID"
type="long"
unsaved-value="0"
>
<generator class="assigned">
</generator>
</id>
because we use standalone application such solution is safe. But in Oracle trace I see that Hibernate still produce many (as many as batch size is):

select hibernate_sequence.nextval from dual

before inserting to my table. How to stop these id generation selects!?

show pool configuration