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
+---