RE: AS/400 STILL wearing the undeserved "closed system" moniker (was PC connection via twinax ?)

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> Joe,  your post in speed neglected the fact that both  OPNQRYF
> and SQL take
> advantage of Indexes (whether created via the CRTLF or CREATE INDEX )

No, I didn't neglect it.  It's just common sense to me that if an SQL read
and a native read use the same index, then there is no way that SQL can be
faster than a native read, unless you posit that somehow the native read
adds some overhead that SQL does not.  I can't think of any reason this
would be the case.

My position, until proven otherwise, is that as long as SQL and LF use the
same indexing, then on a straight keyed read, the only thing SQL can do is
approach the speed of a native read.  It can't possibly get faster.  In
fact, properly written, there's no way SQL access can beat non-constrained
native access.

Where SQL gets its performance boost is in elimination of unnecessary rows,
not in raw database access.  So if you write your application to create
proper indexes and then use those indexes correctly, you'll always have
better performance than the dynamic capabilities of SQL.

Where SQL shines is its ability to dynamically do a lot of the optimization
that was originally the province of a good database programmer.  The better
SQL implementations are smart enough to suggest places where a hard index
will help performance.  Even so, this is just for queries.  Personally, I
have trouble envisioning a situation where using SQL can perform as well as
native access for transaction updates, the meat of any realtime transaction
processing application.

To me this is just common sense, much as there's no way a compiled language
can outperform machine language.  Sure, a good compiler can beat a bad MI
programmer, but a good MI programmer is unbeatable, because at its best, a
compiler finally produces MI - and if the MI programmer knows his stuff, he
can always produce code that is as good or better than the compiler.

I am assuming that SQL reads data from the disk the same way an HLL I/O call
does.  Given that assumption then unless the RPG compiler puts overhead on
the I/O statement that is not there for SQL, there is no way that SQL can
beat properly written native HLL I/O.

Joe