Re: Oracle speed

From: (wrong string) ári Poulsen <kapo_at_utvarp.olivant.fo>
Date: 1997/11/10
Message-ID: <01bcedf1$049e0f60$c9fd24c0_at_uf1.olivant.fo>#1/1


Anthony DeLuca <adeluca_at_fyi.net> wrote in article <34670A91.A4D59F88_at_fyi.net>...
> I am working on a project using Delphi 3 for the
 front end, and
> Oracle 7 server for the back end. We
> are have a problem with speed for doing inserts into
 tables. At certain
> points we are inserting up to
> 100,000 data file records into the database. There is
 information going
> into 3 tables from this database file.
> It takes up to 35 minutes to do an insert this big. The
 current version
> of our software (which uses
> Paradox / C) takes 3 minutes to do this insert.
> Can anybody give me any suggestions on how to go
 about making this
> faster? We are using the native
> BDE Oracle driver to interface the database. I have been
 told about
> stored procedures, but have been
> unsuccessful in using them. Is there some sort of
 software that may help
> us? Is there somewhere we
> can have our code reviewed for optimization suggestions?
 Any help is
> greatly appreciated!!
>
> Thanks in advance,
> --Anthony DeLuca, Prograph Bindery Systems.
>
>
Here are a few pointers:

Look at how the table is organized. Are there many constraints &
indexes?? this slows inserts down. Sometimes it is much faster to drop an index, do the inserts, and regenerate it afterwards. If you make the inserts from Delphi, you might also want to disable the constraints and enable afterwards. Just make sure that the imported data does not violate any constraint.

Use Oracle LOADER if possible. that is, if you just do a straight import from some file

If you have Oracle 7.3 you also can specify that Oracle not uses redo logs for a particular
job. This can really boost performance since the DB doesn't have to keep track of all the SQL statements in case you want to rollback.

hopes this helps..

regards
 Kári Poulsen   Received on Mon Nov 10 1997 - 00:00:00 CET

Original text of this message