RE: SQL vs. traditional I/O?

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: CWilt@xxxxxxxxxxxx
> 
> Joe, the problem is you're comparing apples to oranges.

I put in a semi-randomizer (I bump the counter a couple of thousand
records between reads), and got even more interesting results.  The
first time through, the RPG performs worse than the SQL by a factor of
about 3.7.  Run again (thus reading the same records), the RPG program
improves by a factor of nearly 40, beating the SQL 10 to 1.  The SQL
program does not show significant improvement between successive trials.

The SQL program runs at 70% of CPU for every trial.  The RPG program on
the first trial uses 3% of the CPU.  The second trial runs at 61%.  This
indicates that the RPG program is disk bound until the records are
cached, while the SQL program has pretty much consistent overhead.

This will definitely be something to keep in mind!


> One thing to keep in mind is that the
> situations in an OLTP where you need to fetch one record at a time are
> situations where user interaction is required.  That being the case,
the
> human wait time required is many orders of magnitude higher than RPG
or
> SQL.

This argument doesn't hold.  When you have thousands of concurrent
transactions, human wait has nothing to do with it.  It's the cumulative
processing time that slows down OLTP.  Otherwise, we'd always have
subsecond response time on everything.

And with web services being used as a substitute for simple messaging,
there's MORE of this cumulative overhead rather than less, with boxes
parsing XML documents to access small pieces of information and sending
them back out as XML documents.

 
> The rest of the processing involves set-at-a-time processing, usually
> between multiple files.  That's where SQL is going to shine.

Let's start breaking down our applications and see how much is really
record at a time and how much is really set at a time.  I may be wrong,
but from my time developing ERP applications, there's an awful lot of
record at a time processing.  Now, Joel and Rob may be right that there
are other ways to do things that will change this, but that's what we
need to prove.

Soft arguments like these aren't going to prove anything.  It's time to
get to the brass tacks.


> Two things I try to keep in mind about SQL
> 1) It's not a 1-1 replacement for native I/O.  You shouldn't just
replace
> native I/O in a program with SQL.
> 2) Its for set at a time processing. If you're reading one record from
one
> file, you're probably using it the wrong way.

Bingo.

Joe