Re: performance of dynamic vs. static SQL

Michiel Perdeck <perdeck.via_at_sni.de> wrote in article <335362E2.5EBD_at_sni.de>...
> Currently, our SQL statements are strings (generated by Tcl code) that
> are "prepared" by the Oracle server before being executed. This is
> called dynamic SQL, right?

never mind

> 1. Will the performance of our application improve if we skip one step
> by having our SQL statements prepared in advance (like the precompile
> step when using embedded SQL in a compiled host language)? How?

1. use PL/SQL blocks
2. use stored PL/SQL (procedure, function)
3. use PREPARE CURSOR or something similar

> 2. Is this "static" SQL?

no. Generally speaking even if you use ProC and compile your program into a binary code, it does not guarantee all your SQL statements are "static".
Only those listed above.

> 3. Is that the same as PL-SQL?
> 4. How do we supply variables to the pre-compiled SQL statements?

use bind variables

> 5. Do the precompiled statements reside on the client or on the server?

eventually, only on the server

Cordially,
Sergei  

> Regards, Michiel
>
> --
> Michiel Perdeck
> ------------------------------------------------------------------------
> Siemens Nixdorf: perdeck.via_at_sni.de +31 70 3331530 fax: 3331320
> CMG AT Finance : michiel.perdeck_at_cmg.nl +31 20 5033000 fax: 5033011
> private : michiel.perdeck_at_tip.nl +31 30 6570818 pager: 0659676940
> ------------------------------------------------------------------------
>
Received on Tue Apr 15 1997 - 00:00:00 CDT