RE: Program performance incease ideas

Hi Brian,

 

1.      NEVER specify a logical file in an SQL statement.

The query dispatcher will reroute each SQL statement, that uses a logical
file to the old CQE (classical query enginge), so that it never can take
advantage of the new SQE (Standard Query Engine)

This may cost 10 to 15 % performance

If a logical file is specified in an SQL statement, the query optimizer
looks for the field selection, joins and select/omit clauses. Then it
rewrites the SQL statement.

In the next step all access path (keyed logical files and SQL indexes are
searched for the best access path.

 

2.      Do not use dynamic SQL if static SQL can be used. (and in your case
static SQL can be used!)

With dynamic SQL the access path is newly calculated every time you use the
prepare statement.

This will cause an overhead.

If you use static SQL the ODP (open data path) will not be closed and can be
reused everytime you run the query (until the module or the activation group
end depending what you have specified at CLOSQLCSR option).

If you have to use dynamic SQL, prepare the statement ONCE and use parameter
markers to set your variables.

In the prepare statement a syntax check and other things are performed, so
that is slows down performance.

 

I hope this helps.

 

Birgitta