Re: Compare sql and dynamic sql
Date: 1999/09/30
Message-ID: <ZyMI3.428$9j6.7620_at_news.rdc1.pa.home.com>#1/1
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 - 00:00:00 CEST