Re: Some new results

Hi Joe,

> Just in case anyone is interested, I've just about gotten my
> benchmarking framework in place, and I've already run into some very
> interesting results.  I created a couple of programs, one that just does
> WRITEs, and one that does a number of different SQL techniques: INSERT
> VALUES, INSERT from data structure, INSERT from MODS.

On this topic, Paul Conte ran across the original thread in the MIDRANGE-L
archives regarding SQL vs. Native & benchmarking.  He had some
observations.  The following are his remarks to me -- these are his words
not mine, and I don't necessarily agree with everything he says.


Scott --

I read a bunch of the posts. Some observations:

** For an outdated, but more legitimate benchmark, see: "SQL/400 vs. RPG
IV: Which One's Faster?"  ID: 1956
http://www.iseriesnetwork.com/artarchive/index.cfm?fuseaction=viewarticle&CO_ContentID=1956&channel=art&subart=auth&authid=82

** The lack of basic SQL knowledge is dismaying. For example, Pluta puts a
Declare Cursor statement within a For loop. Seems like some respondents
suggested this would cause a performance hit. Well, Declare Cursor isn't an
executable statement, doesn't belong inside loops, but won't have any
performance impact regardless of where it's coded. The whole red herring
that "SQL isn't meant for record-level access" is BS. SQL is set-oriented,
and a set can consist of 1 (or 0 or more than 1) record. SQL is most
certainly designed to get single-record sets, i.e., single records. SQL
cursors are a mechanism for ordered retrieval (or as an iteration mechanism
for a set), and (especially using the SQL/400 support for array structures)
can perform quite well. Also, when a file has an index over field K and you
use the Open cursor / Fetch by = K / Close cursor technique, SQL/400 does
not generally actually open and close the cursor -- it just repositions the
file pointer. And, one last point, nobody seems to have mentioned that IBM
has completely replaced the OS/4000 query engine and this new engine is
used only for SQL access. All future IBM development efforts are going into
this new engine, so "native" I/O is sitting on system code that won't see
any improvements.

You may want to pass along that I've already written extensively on "SQL
vs. Native" in the Sep 6, 2001, Sep 20, Oct 4, Oct 18, and Nov 1 Club Tech
newsletters. (also a follow-up in Nov 15.) The series starts here:
http://www.iseriesnetwork.com/resources/clubtech/index.cfm?fuseaction=ShowNewsletterIssue&ID=11358

And, of course, my SQL/400 Developer's Guide and Powertips for iSeries
Database and SQL books (available from Penton) cover a lot of SQL basics.

Thanks again!

-- Paul