performance of dynamic vs. static SQL

performance of dynamic vs. static SQL

Post by Michiel Perdec » Wed, 16 Apr 1997 04:00:00



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?

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?
2. Is this "static" SQL?
3. Is that the same as PL-SQL?
4. How do we supply variables to the pre-compiled SQL statements?
5. Do the precompiled statements reside on the client or on the server?

Regards, Michiel

--
Michiel Perdeck
------------------------------------------------------------------------



------------------------------------------------------------------------

 
 
 

performance of dynamic vs. static SQL

Post by Dragon Fl » Wed, 16 Apr 1997 04:00:00




Quote:> 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

Quote:> 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

Quote:> 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.

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

use bind variables

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

eventually, only on the server

Cordially,
Sergei

> Regards, Michiel

> --
> Michiel Perdeck
> ------------------------------------------------------------------------



> ------------------------------------------------------------------------


 
 
 

1. Dynamic Files vs Static Hashed - Performance Issues

I don't want to provoke another monster thread on this. Can anyone point me
at a documentation/discussion regarding the performance penalties associated
with dynamic files in Universe.

I don't necessarily want a discussion of linear hashing algorithms or file
structure, I'm looking for (ideally) numbers showing comparitive
read/update/select times for dynamic vs static, particularly for files
exceeding 1GB/1M records.

Barry.

2. PDOXWIN 1.0 FORMS CORRUPTION

3. Dynamic SQL Vs Static SQL using Pro*C..

4. SQL7 Beta3 DSS - How can I save and restore defined dimensions/cubes ?

5. STATIC vs. DYNAMIC SQL and ODBC

6. Data Control

7. Dynamic vs Static Stored Procedures

8. [Help:Patch for International Use]

9. Dynamic filter vs static filter...

10. Static vs Dynamic Recordsets

11. Dynamic vs. Static

12. Dynamic vs. Static Hashed Files

13. Static vs. Dynamic ESQL