RE: sql exec speed. was AS/400 STILL wearing the undeserved "closed system" moniker

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

We'll just have to agree to disagree here, for two reasons:


1. On updates and inserts, the difference between embedded SQL and native
I/O is not a couple of percentage points.  Last year I ran some performance
benchmarks that showed that an embedded SQL INSERT was two orders of
magnitude slower than a WRITE.

I just reran the test... on V4R5, comparing a native WRITE opcode to the
embedded SQL statement: INSERT INTO J4PP1 VALUES(:KEY1, :KEY2, :DATA1,
:DATA2).

100,000 WRITE operations took 7 seconds
  1,000 SQL INSERT INTO ... VALUES operations took 10 seconds

0.07 msec/WRITE
10.0 msec/INSERT

Now I'm no SQL expert, and the tests were crude, so I'm sure there are
better ways to do this.  Please detail the syntax you would use to do an
insert and an update.  Because in this case, I'm looking at over two orders
of magnitude, which certainly doesn't argue for SQL.


2. Even if SQL is "close" in performance, it's not good enough.
Milliseconds do make a difference.  While I was the manager of architecture
for BPCS, the primary issues we dealt with were to make sure that our order
entry systems were as fast as possible, and that our MRP generations ran as
efficiently as possible.  If an order requires several hundred I/O's (not at
all unusual in a large order with promotions and deals pricing in place),
then those milliseconds quickly become seconds.  Even more so on an MRP
generation, where millions of records are processed - an efficient
generation algorithm means a run of hours as opposed to days.


Until these issues change, SQL is the wrong tool for transaction updates.

Joe


> -----Original Message-----
> From: midrange-l-admin@midrange.com
> [mailto:midrange-l-admin@midrange.com]On Behalf Of John Taylor
> Sent: Sunday, August 19, 2001 1:27 PM
> To: midrange-l@midrange.com
> Subject: RE: sql exec speed. was AS/400 STILL wearing the undeserved
> "closed system" moniker
>
>
>
>
> > -----Original Message-----
> > From: Behalf Of Joe Pluta
> >
> > > update pricefile set unit_price = unit_price * 1.05
> > >    where product_type = "widgets"
> >
> > I've seen this example in so many SQL references as to make me
> > ill.  This is
> > a very rare occurence for every business I've seen.
>
>
> It's not at all unusual in industries where pricing is based upon the
> commodities markets.
>
>
>
> >
> > Yes, and for those things, SQL is good.  For transaction
> > processing, SQL is
> > bad.  For queries and wholesale data replacement, SQL works wonders, but
> > when programmers start using it to update data (especially a record at a
> > time), that's when I shake my head.
>
>
> Why? If you're achieving sub-second response time with the SQL
> update, does
> it really matter that a native opcode would have taken a few milliseconds
> less to complete? In an interactive TP application, the difference isn't
> noticable.
>
>
>
> John Taylor