AW: SQL, Logical Files, Unions & Indexes.

Hi Joe,

SQL performs better than native I/O for many queries, but it definitely

does NOT perform better for single record reads, writes or updates.

Sorry for not being correct:
I agree for single record access is RLA faster (at least if a SETLL is
used).  
If an Index only access (all needed information can be found in the key and
no additional access to the database is necessary) can be used SQL and RPG
Chain are almost identical.

For update and write I'm not sure either. If an update is performed with
WHERE CURRENT OF and the same ODP as for the cursor can be used, there
should not be any remarkable difference. If the update statement needs its
own ODP (for example if a calculation/formula is specified or a special
register is used) the full optimization must be performed (at least at the
first and second time this statement will be executed), which is very
expensive.
For inserting a single row I'm not conviced either. Specifying REUSDLT
*YES/*NO will have more effect.
SQL will always be slow, when a full open (creating/validation an access
plan, finding the optimal index, creating any needed temporary objects) must
be performed. A full open will always happen the first two times an SQL
statement will be performed. After the second execution the ODP stays open,
as long as it is reuseable and CLOSQLCSR *ENDMOD is not specified in
(service-)programs, the library list gets not changed ... . When reusing an
ODP only the data must be actualized.

In either way if you want to compare SQL and RLA it must happen after the
second execution. The first two runs SQL will  lose.

MOST of the time views are a better alternative than logical files

(although logical files can act as both index and view).
I got better performance when using SQL views instead of directly joining
the tables in my programs.
With more powerfull I meant, everything that is possible with a
SELECT-Statement (except order by) can be specified in a view (even
recursive execution is possible).

For SQL tables and DDS described physical files there is an architectural
difference. In DDS described files data validation happens as soon as a
record will be read (that's why garbage can be copied into physical files
with *NOCHK). In SQL tables data validation happens as soon as a row will be
written, but there is no validation when a row will be read. Even with Copy
*NOCHK no invalid data can be inserted into a SQL table. Because data
validation occurs at write time and not at read time, using SQL tables will
gain performance.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Joe Pluta
Gesendet: Sunday, April 01, 2007 16:14
An: 'Midrange Systems Technical Discussion'
Betreff: RE: SQL, Logical Files, Unions & Indexes.

From: BirgittaHauser

Instead of using joined logical files, you should create SQL views 
instead. Views are much more powerfull than DDS described logical 
files.

Let's be careful here.  MOST of the time views are a better alternative than
logical files (although logical files can act as both index and view).  In
fact, for most cases you should really consider using DDL to describe all of
your files, including physicals, because of the benefits of DDL.  But this
is different than SQL.

And with the right indexing strategie SQL performs much better than 
native I/O even if you are only working with DDS described physical 
and logical files.

And here you must be very careful.  SQL performs better than native I/O for
many queries, but it definitely does NOT perform better for single record
reads, writes or updates.

Joe