RE: SQL vs "native" I/O performance

I'm getting off topic here (or maybe not) but here goes.

We have a VB application that connects either to the AS/400 or a SQLAnywhere
database (using ODBC) depending on the type of user.  The database is pretty
normalized and most of our SQL is in stored procedures.  About 150 tables,
triggers on about 1/3 of them.  Two complaints:

Major complaint:

Every OS upgrade, starting with 4.0 to 4.1 has blown up on us.  SQL
performance goes in the toilet.  After many calls to Rochester, having them
monitor our system, send service job output, actually giving them our tables
and stored procedures, etc. they think they know what happened.  Next is
several rounds of ptf's,  and other 'tweaks' to the system (Data area
QQQOPTIONS apparently did not do much in release 4.1, killed us in 4.2) and
so on.  Eventually (after 1-3 weeks of bad performance) we are up and
running again.  

We just purchased a new 'mixed mode' server to consolidate our production
environments.  It came with 4.3 loaded...  and performance went in the
toilet.  Same story.  Escalate to CritSit, daily discussions, sit-downs with
technical folks,  and eventually they think they know the problem.  Several
ptf's, (and a new ODBC DLL) later, and we are up and running.  This time it
was months, not weeks.

IBM says this is not typical, but it has become the norm for us.  Do any of
you have these problems?

Minor complaint

SELECT * FROM BIGTABLE, LITTLETABLE 
  WHERE BIGTABLE_KEY = LITTLTABLE_KEY

performs much differently on the AS/400 than 

SELECT * FROM LITTLETABLE , BIGTABLE 
  WHERE LITTLETABLE_KEY = BIGTABLE_KEY

(oversimplified example)
Other databases appear to perform them equally well.

We are forced to go through all of our SQL, both embedded and stored
procedures, re-structuring for performance.  


Bottom line on both issues is that it appears to be taking IBM a long time
to 'ramp up'.  Will we see the same learning curve on Java (getting back on
topic)?

End of rant.
Thanks, JN


PS  Mr Conte, I remember that article and it was very informative.  In fact,
I used it to help sell management on using the AS/400 as the server, not
just the repository and back-end processor.

> -----Original Message-----
> From: Paul Conte [SMTP:pconte@picante-soft.com]
> Sent: Wednesday, May 05, 1999 12:15 AM
> To:   JAVA400-L@midrange.com
> Subject:      RE: SQL vs "native" I/O performance
> 
> >Joe, have you done any testing to see if your stuff is as fast, faster,
> >whatever, compared to SQL?
> >--------
> >Not a lot, but I don't need a lot.  Other than the magic of stored
> procedures
> >(which I'm not too familiar with, but the last time I looked was pretty
> >non-portable), there's just no way SQL can match up to native AS/400
> database
> >I/O for true transactional stuff.  It's great for queries, hence it's
> name, but
> >it's lousy for transaction processing (which is probably why it's called
> "SQL"
> >rather than "STPL").
> >
> >Anyway, a simple inconclusive test:
> >
> >Inserting 10000 records into a file consisting of two L-dates and a
> character
> >field.
> >
> >SQL:      17 seconds
> >Native:   2 seconds
> >
> >There's no contest.
> 
> Anyone interested in a more in-depth analysis of SQL vs. "native" I/O
> might
> want to look at "SQL/400 vs. RPG IV -- Which One's Faster?" (which I
> authored) in NEWS/400, September 1996. This reports V3R6 results, so is
> obviously outdated, and I don't claim it represents V4R3 (or R4) results.
> However, in my own, less rigorous, experience since conducting those
> benchmarks I have the impression that SQL performance has improved more
> than
> "native" I/O.
> 
> In general, the benchmark results show that SQL and "native" RPG IV
> performance are close enough that both are viable for many "real world"
> situations. OTOH, there are some specific cases which a developer should
> be
> aware of for "heavy" use.
> 
> The complete article explains the methodology (which was fairly rigorous)
> and full results for a variety of tests. Here are a few examples: for bulk
> inserts, SQL was about 12% faster than "native" RPG IV. For bulk, keyed
> sequential retrieval of selected columns, SQL was about 3% faster. For
> direct retrieval of a single row by key, "native" was about 55-65% faster,
> depending on the number of columns retrieved. For retrieving a set of rows
> with a common high order key column value (e.g., a customer's orders),
> "native" was 15-20% faster.
> 
> IBM is clearly investing in SQL as the strategic database interface to DB2
> UDB for AS/400 (and, of course, SQL is the only serious database interface
> for all other major RDBMS). Looking at the functional, performance, and
> standardization aspects, I would strongly advise companies to use SQL as
> their AS/400 database interface, except in those specific cases where a
> compelling case can be made for using "native" HLL I/O. If I/O performance
> is truly a big issue, ILE COBOL should also be evaluated, as it has
> traditionally performed better on some (but not all) types of "native"
> I/O.
> 
> A footnote: There are a few techniques that may significantly affect SQL
> I/O
> performance (e.g., using blocked Fetch's), so it's important when
> reviewing
> or conducting benchmarks to make sure you're considering the most
> effective
> way to use SQL. The data in the article also illustrate some of the
> performance differences in various SQL techniques.
> 
> And another: The SQL stored procedure language is part of the ANSI
> standard,
> although this standard doesn't seem to be fully implemented on all the
> major
> RDBMS. There are a bunch of issues (functional and performance, as well as
> standards) to consider in chosing whether to use SQL or an HLL to
> implement
> an AS/400 SP.
> 
> -- Paul
> 
> +---
> | This is the JAVA/400 Mailing List!
> | To submit a new message, send your mail to JAVA400-L@midrange.com.
> | To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to
> JAVA400-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
> david@midrange.com
> +---
+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---