Re: Differences in performance of an INSERT-statement in SQL and PL/SQL

The first is a single insert of n records, the second is n inserts of a single record.

Its not PL/SQL that is slow. In SQL*Plus a single insert would be equally quicker than a script with muliple insert statements of a single record. Alternately a single insert coded within PL/SQL would perform just as quickly, as if it were issued elsewhere.

Single SQL statetements are always a lot faster than procedural code that performs an equivalent function, whatever the programming language or environment.

The only cases where I have not found that not to be true is where a single result set can be split and then executed in parallel, but that has been rare.

Martin Burbridge

joern.dinkla_at_promatis.de (Joern Dinkla) wrote:
>
> I stumbled into the following on Oracle 8.1.6.0.0.
>
> The following statement
>
> INSERT INTO table
> SELECT bla
> FROM ...
> WHERE currency = 'EUR' and period = '01-01' and sid = 1
>
> takes about 320 seconds for a table with 4000 rows and 15 columns
> when called from sqlplus or sql navigator.
>
> Ok, but when put it into a pl/sql package and abstract some parameters
>
> FOR rec IN cur_rec
> LOOP
> INSERT INTO table
> SELECT bla
> FROM ...
> WHERE currency = rec.currency and period = rec.period and sid = rec.sid
> END LOOP;
>
> and call it the insert-statement takes 500 seconds for the same table.
>
> What could be the cause for the difference of 180 seconds ?
Received on Thu Feb 21 2002 - 19:38:23 CST