Re: benchmarking, which statement is faster

"DS" <danny118_at_hotmail.com> wrote in message news:44ff9953.0107241139.496f2ddf_at_posting.google.com...
> hi all,
>
> has anybody benchmarked or has any idea which of the
> following statements will execute faster?
>
>
> a) INSERT INTO TABLE_X (field1,
> field2,
> field3,
> ...
> ...
> ...
> fieldn)
> SELECT source_field1,
> source_field2,
> source_field3,
> ...
> ...
> ...
> source_fieldn)
> FROM TABLE_Z, TABLE_A
> WHERE xx = yy
> AND zz = aa;
>
> b) CURSOR my_cursor IS
> SELECT source_field1,
> source_field2,
> source_field3,
> ...
> ...
> ...
> source_fieldn)
> FROM TABLE_Z, TABLE_A
> WHERE xx = yy
> AND zz = aa;
>
> my_record my_cursor%rowtype;
>
> OPEN my_cursor;
> LOOP
> FETCH my_cursor INTO my_record;
> EXIT WHEN my_cursor%NOTFOUND;
>
> INSERT INTO TABLE_X (field1,
> field2,
> field3,
> ...
> ...
> ...
> fieldn)
> VALUES ( my_cursor.source_field1,
> my_cursor.source_field2,
> my_cursor.source_field3,
> ...
> ...
> my_cursor.source_fieldn);
>
>
> END LOOP;
>
> CLOSE my_cursor;
>
> Note:
>
> 1) The select statements are the same.
> 2) source_field[1..n] can be a simple field from a table
> or the value returned by the invocation of a database function.
>
> thanks.

a will be definitely much faster as everyone is done by the database, there are no context switches involved (switching from the pl/sql engine to the sql engine), and ordinary pl/sql doesn't support array fetching. So I wouldn't bother benchmarking it, as I know it is much faster.

Next time, will you please consider *not* crossposting to all Oracle newsgroups, especially not to comp.databases.oracle especially as not every ISP supports that?

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Tue Jul 24 2001 - 22:03:51 CEST