>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: email@example.com +---
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.