RE: SQL vs native access (was: Record name the same as the filename)

Joe,

Let me offer this based on past experience:

You'll find that V5R1 is faster. Query optimizer tuning has happened on every 
release and I've seen it get steadily better over the past couple. We're still 
on V5R1 so I can't comment on V5R2.

If you want to start using a lot of SQL, the SQL books are well worth the time 
to read. One of them (it's been a while since I read it so I don't remember 
which one it is) discusses the query optimizer. It's a slow read and you'll 
probably want to experiment with different SQL statements as you read it but 
it's well worth the time.

Proper indexes are key to good performance. Running the SQL in debug mode will 
return query optimizer messages that will tell you when you need to either 
create additional indexes or restructure your queries to take advantage of 
existing indexes. Knowing how the query optimizer works will help you decide 
which one you need to do or if you can just ignore the message. The query 
optimizer isn't perfect and I've seen query performance degrade when you give 
it exactly what it wants in some cases. I've also seen where indexes and/or 
compile settings need to be a little different based on if the box has SMP 
installed or not.

For example, we have an order search program that if ALWCPYDTA is set to 
*OPTIMIZE (this is the default), performs horribly when called interactively on 
a box with SMP (it screams in batch, like when it's called from our web 
server). Changing this to *ALL makes it perform very well in both cases (it is 
a little slower in batch but it's still better than what it replaced). In doing 
performance tuning on this, we also went from a join on two files to building a 
cursor over the first and chaining to the second. This performed better than 
the program it replaced which used all native I/O (of course, that was a JD 
Edwards program so it's not much of a surprise). The biggest benefit we got 
doing it this way was that the program is easier to understand now. We also 
changed from doing random access via a select to using native I/O because we 
found the file we were going after was being opened and closed each time we hit 
it with SQL (one thing I want to mention was the difference in time was 
negligible until it was processing millions of records, it normally only hits a 
few thousands and in this case, the difference was less than a second). Opening 
and closing files are very expensive operations.

I guess the point I'm trying to make is that it's very easy to write poorly 
performing SQL and you really need to understand the optimizer to get 
consistent performance. I will also add that when accessing a single record 
located on the same machine, it's hard to beat native I/O.

Matt

<snip>
Haven't run them yet on V5R2, Mike.  My last tests were on V4R5.  But
I'd be flabbergasted to learn though that a CHAIN from a native record
would be slower than a SELECT/FETCH (there's just more overhead on the
latter).  The problem though is that in order to be fair I really need
to use the fastest syntax available and that would require working with
a good SQL expert.  The first step would be to at least set up a set of
criteria that we could all agree on to compare performance.
<snip>