home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: Product
/
Product.zip
/
DBDEMO.ZIP
/
DEMOFLS.ZIP
/
STAT_DYN.TXT
< prev
Wrap
Text File
|
1991-07-01
|
2KB
|
44 lines
Static vs. Dynamic SQL
This program shows the performance difference between static
and dynamic SQL. The query has been specially chosen to be
complex (lots of clauses) but short (only one row is FETCHed)
In the static case the CURSOR is declared explicitly by writing
the whole SQL statement after EXEC SQL. In the dynamic
version, the same query is first copied into a string variable
and then PREPARED. It is assumed (maybe unrealistically) that
all the column names are known, otherwise an SQLDA has to be
used making it more complex.
If the complete SQL query is known beforehand, it is usually
more efficient to use static SQL. For queries known only at
run time (e.g. the number of columns or the table name has to be
user defined), dynamic SQL is required.
At its most complex form, nothing is known about the query
before hand, so the query, table names, and the columns to
be extracted have to be determined at run time using SQLDA,
PREPARE and DESCRIBE.
Use of same cursor for Static and Dynamic is allowed.
┌───────────────┬───────────────────────┬──────────────────────────┐
│ Item │ Static │ Dynamic │
├───────────────┼───────────────────────┼──────────────────────────┤
│ Preparation │ Implicit at SQLPREP │ Explicit at Runtime │
├───────────────┼───────────────────────┼──────────────────────────┤
│ Persistence │ Until rebound │ Until COMMIT or ROLLBACK │
├───────────────┼───────────────────────┼──────────────────────────┤
│ Authorization │ Binder │ Executor │
├───────────────┼───────────────────────┼──────────────────────────┤
│ Otimization │ Present at SQLPREP │ Current │
├───────────────┼───────────────────────┼──────────────────────────┤
│ Input/Output │ Host var(except FETCH)│ Host var or SQLDA │
└───────────────┴───────────────────────┴──────────────────────────┘
CONCEPTS:
Access plan and its preparation, dynamic SQL, PREPARE, DESCRIBE,
SQLDA (for more advanced dynamic queries).