Re: Performance of Multiple Inserts

Drop your test data tables.
Remove index and contration creation from your script. Creat the tables.
Load the data.
Create your indexes.
Alter table to create your constraints.

David Roberts (droberts_at_seas.gwu.edu) wrote:
:> A friend of mine has written a simple Oracle program that he thinks
:> should run in a couple of hours, that now appears to need 48 days to
:> complete! I offered to ask for advice here....
 

:> The program is a simple load utility, loading a lot of payroll data into
:> a database. The program is generating test data, so it just does
:> inserts. At present, it COMMITs after every insert. In an effort to
:> speed things up, the program was run simultaneously from four clients
:> accessing the same server, and there was about 3 x speedup, but we are
:> still talking about 16 days to build our test database!
 

:> A couple of questions--
 

:> 1. I suspected that the program might run faster if it committed about a
:> whole pageful of data and then COMMITted, rather than issuing a COMMIT
:> after each INSERT. What do you think?
 

:> 2. Would that same strategy help for the multiple-client case? Are the
:> multiple clients each going to work with the same page of data, hence
:> have to wait on each other, or will Oracle give each of them a separate page?
 

:> Thanks
 

:> Dave Roberts

--
This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.