Dynamic SQL 
Author Message
 Dynamic SQL

Currently I'm in the process of designing a COBOL program to interface
with an Oracle Database.  I'm looking at simply retrieving the data from
the tables via relatively simple SELECT statements.  However, I was
trying to compare the differences between static Embedded SQL statements
and Dynamic SQL statements.  From what I have read, I realize that
Dynamic SQL provides open ended flexibility in the program that I am
writing, however if I know all the possible queries that I can run, what
benefit would I get from using Dynamic SQL.  In addition, are there any
performance issues that I should be aware of when comparing static
Embedded SQL and Dynamic SQL.



Sat, 02 Oct 1999 03:00:00 GMT
 Dynamic SQL

Quote:

>Currently I'm in the process of designing a COBOL program to interface
>with an Oracle Database.  I'm looking at simply retrieving the data from
>the tables via relatively simple SELECT statements.  However, I was
>trying to compare the differences between static Embedded SQL statements
>and Dynamic SQL statements.  From what I have read, I realize that
>Dynamic SQL provides open ended flexibility in the program that I am
>writing, however if I know all the possible queries that I can run, what
>benefit would I get from using Dynamic SQL.  In addition, are there any
>performance issues that I should be aware of when comparing static
>Embedded SQL and Dynamic SQL.

Just use the static SQL, since you don't need the dynamic
capabilities.  I haven't compared the two methods, but the dynamic
version could only be slower.  And the coding to run the dynamic
version would be more difficult.  Why use it if it is not needed?

HTH
Steve Cosner



Sun, 03 Oct 1999 03:00:00 GMT
 Dynamic SQL

Quote:


> > (snip)

> Just use the static SQL, since you don't need the dynamic
> capabilities.  I haven't compared the two methods, but the dynamic
> version could only be slower.  And the coding to run the dynamic
> version would be more difficult.  Why use it if it is not needed?

> (snip)

As a matter of fact, there is *no* run-time difference in performance
between dynamic and static SQL and, yesy, I *have* measured it. This is
because both are "dynamic" from the kernel's point of view: i.e. it
receives a string to be parsed: it doesn't know or care whether the
string is composed piecemeal or is in the source program as a complete
string.

It is probably easier to maintain "static" statements in the code, but
if you have a lot of nearly identical statements that can only execute
one at a atime, it may be better to use a single cursor to process one
of a number of dynamically-prepared versions at a time instead of
declaring a number of different cursors.

Chrysalis.

Chrysalis.



Thu, 07 Oct 1999 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Dynamic SQL accessing dynamic temp tables

2. Dynamic Execute Statement vs. Passing Dynamic SQL

3. Dynamic order by without dynamic sql ?

4. DYNAMIC SQL MVS DB2 V6 DYNAMIC WHERE

5. PL/SQL:Efficiency of inline SQL vs use of Dynamic SQL Package

6. t-sql cursor with dynamic sql

7. SQL 2000 User Defined Function with Dynamic SQL

8. Dynamic SQL in SP Faster than Compiled SQL??!!!

9. Q : Dynamic SQL in sql server 6.5

10. SQL Server 6.5 Dynamic SQL inside Trigger to Access deleted Table

11. Dynamic SQL generation in T-SQL

12. SQL HELP...Dynamic SQL?


 
Powered by phpBB® Forum Software