RE: SQL, Logical Files, Unions & Indexes.

From: BirgittaHauser

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.

I might agree here.  I'll have to re-run all of my benchmarks later.  But
then again, it's rare that we CHAIN without wanting to look at data.  I'd
say a few times out of a hundred I may CHAIN just to see if a record exists
(and of course, I would never CHAIN in that case, I would just SETLL).

For update and write I'm not sure either.

I am.  Single record updates even with "where current of" do not perform
nearly as well as native updates.  I've run benchmarks as recently as V5R3,
and got the following results (these are number of seconds to process 10,000
records):

SIMPLEIO      15   Update Test - SQL-C (100 ROWS)              1.010 
SIMPLEIO      14   Update Test - SQL-C (10 ROWS)               1.424 
SIMPLEIO      13   Update Test - SQL-B (UPDATE SINGLE)         4.480 
SIMPLEIO      12   Update Test - SQL-A (CURSOR/FETCH)          2.346 
SIMPLEIO      11   Update Test - Native I/O                    1.135

SIMPLEIO       5   Insert Test - SQL-C (100 ROWS)              1.456  
SIMPLEIO       4   Insert Test - SQL-C (10 ROWS)               1.903  
SIMPLEIO       3   Insert Test - SQL-B (SINGLE DS)             3.844  
SIMPLEIO       2   Insert Test - SQL-A (VALUES)                3.901  
SIMPLEIO       1   Insert Test - Native I/O                    1.735  

In general, native I/O runs roughly twice as fast as SQL.  And while I
expected the UPDATE by key to perform more poorly than the UPDATE using a
cursor, I was surprised that the INSERT using a data structure was nearly as
slow as the INSERT VALUES.

I will run these again when I upgrade to V5R4.

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.

Exactly.  Except in those rare circumstances where you do more writes than
reads (journals and logs), DDL should outperform DDS.

Joe