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).