Re: Batching creation of primary keys with Oracle
Re: Batching creation of primary keys with Oracle
- Subject: Re: Batching creation of primary keys with Oracle
- From: Wiktor Moskwa <email@hidden>
- Date: Mon, 28 Apr 2008 10:58:55 +0200
Ken Anderson pisze:
> It's definitely the primary key fetch. We're saving batches of 100+
> objects, where 90% of the objects are the same entity. It iteratively
> calls the Oracle sequence to get them. Granted, Oracle is very fast in
> doing this, but it's still adding up to a significant amount.
>
Hi Ken,
One problem is that EOF fetches sequence value first and then inserts a row.
In Oracle the "right way" would be to do this in a single statement:
insert into xxx(xxx_id, ...) values (xxx_seq.nextval, ...)
or if you want to know what values were generated:
insert into xxx(xxx_id, ...) values (xxx_seq.nextval, ...) returning xxx_id into ...
The other problem I see is that application server is not the best tool
for batch processing. If data is generated by the batch process I'd use
PL/SQL, if data comes from the outside I'd use SQL Loader or external tables
to load it and if data has to come in batches through application server I'd
consider direct path insert - "insert /*+append*/ ..." statements (but be careful
about some consequences of direct path operations).
My advice is to take a look at Oracle wait interface for this particular session
(by enabling SQL Trace and formatting output with TKPROF) to see what takes the
biggest amount of time.
Wiktor Moskwa
--
Power Media S.A.
Wroclaw, Poland
http://www.power.com.pl
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden