RE: SQL vs native access

Based on my experience using SQL on the AS/400, I have used it since it
first became available (slow as mud back then), that since V4 for the
OS/400 SQL performance has been so close to the tradition access methods
and with each release after that the difference has become so small that
it isn't an issue of speed anymore. The real issues should be what makes
the best sense for what you are trying to accomplish with the
application. 
 
I have used the AS/400 for data warehousing and I used a mixture of SQL
and Native access. The performance was very important in moving and
massaging the data.  Here is just a summary of what I found.

1. Moving chucks of data from 1 file to another and excluding records
during the read process using RPG with big fields and pointers beat CPYF
and SQL. (It beat the CPYF very bad which surprised me. Even though it
beat SQL it wasn't over huge, but the code looked cleaner and I didn't
have to have any indexes to worry about creating for the load process so
I went with RPG.)

2. When it came to massaging the data from several files to many other
relational files, SQL was faster (and I mean way faster), less code, by
looking at the SQL statement it easier to see what it was doing vs
reading RPG that needed to loop and chain to how ever many files and
having to code to skip records it didn't need.

3. Slow SQL performance that people fuss about on the AS/400 isn't from
SQL 
being slow, it is from lack of knowledge of how SQL works and AS/400
programmers try to code SQL like RPG, COBOL, etc... Which frustrates
them and SQL on the AS/400 gets a bad rap. Anytime someone has brought a
slow running SQL to me, the performance was corrected with writing the
SQL correctly or adding a correct index.

4. Poor coding methods with traditional RPG and COBOL using Logical
Files with select/omits cause poor performance issues with SQL. The
reason this causing poor SQL performance issues is that when an SQL
statement of something like Select F1, F2, F3 from File1 Where F1 =
123456 and F8 > 0
Order by F1, F2, F3 - The query manager starts looking at the lf/indexes
for the file/table. If the files/tables index aren't created right and
it has too many lf/indexes it can't use because the lf have select/omits
this can cause the query manager to quit looking a do a full table scan
or build a temporary index. Some of these problems can be avoid by
building all the lf/indexes that don't have select/omits first and
creating lf/indexes that can force sharing of indexes between other
indexes. 

5. Poor database design causes other performance example. File1 is
linked to File2 with four fields. In RPG, reading File1 and chaining to
File2 performs well and we are all use to that model. There are three
things wrong with this database design 1 is waste of disk space do
duplicate data in File2, File1 should have one field that contains
unique value that is the key to File2, the lf/indexes wastes disk space
because of the key size where the 1 unique filed key value makes the
index size smaller. We all database like this in some form or fashion.

6. Don't know why IBM did this but creating the indexes with SQL are
better than the DDS. There are articles put out by IBM on why, but still
seems like the DDS would yield the same result. But I have seen
performance go up by doing this.


7. I see where several people have said that SQL isn't fast a chain to a
file. I don't totally agree with that based on this fact. If you are
using every field in a file that you do a chain on then I would say the
chain would probably be faster. However, if you are only using 15 - 25
fields out of a 200 field table, then I would lean to SQL being faster
because it doesn't have to return data that isn't being used. That is
document pretty well in the SQL manuals and this relates back to bullet
1 and 2. It really depends on if the application needs all the I/O
performance you can provide it that would lean me to pick one over the
other.

8. Last but not least. If you have a multi-processor box, buy SMP. This
feature alone can do miracles for SQL performance and if you need to
rebuild LF after a reorg on the table it helps there as well. SMP allows
the SQL statement to be split up among the processors. Example of
performance - I had a table that once loaded I needed to build 10
lf/indexes on it. Without the SMP turn on it took about 2 hours to
build. With SMP turned on, it took less than 30 minutes. That is a huge
difference and those are numbers from back on V4. Kent M. with IBM has
published a recent article new benchmark numbers with V5R2 located on
the DB2 website. FYI doesn't do anything for the tradition access
programs.  



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Friday, August 22, 2003 3:20 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: SQL vs native access 

> From: Walden H. Leverich III
> 
> I think that was true prior to 5.2. In <=5.1 SQL actually accessed
data
> through the same SLIC primatives as single record operations. However,
in
> 5.2 SQL has a new query engine (below the MI) and its own set of SLIC
> primatives so I'm tempted to say that common sense may see a change.

According to Jon Paris' post, this doesn't seem to be the case, Walden.
Do you have any type of formal information on this, or are you just
guessing?

Joe

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.