RE: sql exec speed. was something else

[Jim Damato <jdamato@xxxxxxxxxxxxxxxxx>]

> SETLL/READE implies that you're going to process records in a set, like
all
> the items on an invoice.  RPG will still read them one at a time like it
> always did (aside: blocking can play a huge factor) but SQL is designed
> around the idea of processing the entire set at once.

Got it.  I was missing the argument.  You're comparing a SETLL/READE (or
CHAIN/READE) loop by key to an SQL request for the same data by key.

Aren't you really trying to compare apples to tube socks here?  Depending on
what you're doing the SQL request could be trivial or complicated.  If
you're running a report through a report writer architected for SQL it's a
slam dunk.  If you're doing a simple update (setting the order status to
closed for every line item on a P.O.) it wouldn't surprise me if the single
SQL update (with the appropriate supporting index) ran more efficiently than
the RPG READE/UPDAT loop.  If you're updating with calculations or logic in
the loop it wouldn't surprise me if the READE/UPDAT loop ran better than
extracting the SQL results from a buffer or setting a cursor.

I've always believed that there were many business data processing
requirements that SQL just can't do well.  For those applications processing
a record at a time by key seems to be more efficient, and the code looks a
lot cleaner.  I'm starting to see that some of those business calculations
could be performed cleanly using SQL if the database were designed better,
or at least designed with SQL in mind.

-Jim

James Damato
Manager - Technical Administration
Dollar General Corporation
<mailto:jdamato@dollargeneral.com>