Re: Compare sql and dynamic sql

The only overhead the I can think of is the creating of the statement itself. See as follows. I also don't know how you would come up with the names of the numbers, but see below.

declare
  csr integer;
  rslt integer;
  mysql varchar2(200);
begin

• open the cursor; csr := dbms_sql.open_cursor();
• loop through numbers (chose 200 as end) for x in 1..200 loop
  • create the sql statement mysql:='insert into foo_1 values('||x||','||x||')';
  • the following would be the same either way begin
    • parse statement dbms_sql.parse(csr,mysql,dbms_sql.native);
    • if here, go ahead and execute. rslt:=dbms_sql.execute(csr); exception when others the null; -- add real exception handling here. end;
  • end of things that are the same end loop;
• close the cursor, done. dbms_sql.close_cursor(csr); end; /

The parsing and executing are similar, but the actual creation of the statement to execute would be overhead.

Jay <mighty_dragon_at_hotmail.com> wrote in message news:37F37FD5.1D9B1096_at_hotmail.com...
> For a large number of sql statements w/c contain a pattern, for example:
>
> insert into foo_1 values (1, one);
> insert into foo_2 values (2, two);
> insert into foo_3 values (3, three);
> ...
> insert into foo_n values (n, n);
>
> Someone had claimed that the above execution is faster than any means
> like using stored procedures with pl/sql OR dynamic sql OR native
> dynamic sql.
>
> Can anyone explain any reason why using stored procedures would be
> slower.
>
> Is there any overhead using dynamic sql over straight sql???
>
> In any case, can anyone refer me to a document that will explain in some
> details how dynamic sql execute internally.
>
>
>
> Thanks in advance for any help.
>
Received on Thu Sep 30 1999 - 11:52:09 CDT