× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

 


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.