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

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

I didn't believe my own tests, so I tried again.  It turns out I had
commitment control active on the SQL.  I removed it.

Second WRITE Test:

 100,000 WRITE operations took 7 seconds
 100,000 INSERT INTO ... VALUES operations took 40 seconds

 0.07 msec/WRITE
 0.40 msec/INSERT

Not two orders of magnitude, but still a very poor performance from SQL.

UPDATE Test:

 100,000 READ/UPDAT operations took 10 seconds
 100,000 SQL FETCH NEXT/UPDATE WHERE CURRENT operations took 40 seconds

 0.10 msec/UPDAT
 0.40 msec/UPDATE

A little better, but nothing to write home about.  My code was as follows:

C                     DO   COUNT
C                     READ J4PP1R                   90
C                     MOVE INDEX     KEY2
C                     UPDATJ4PP1R
C                     ENDDO

-vs-

C/EXEC SQL
C+ DECLARE C CURSOR FOR SELECT * FROM J4PP1
C/END-EXEC
C/EXEC SQL
C+ OPEN C
C/END-EXEC
C                     Z-ADD2         INDEX   50
C                     TIME           TIME1   60
C                     Z-ADDCOUNTI    COUNT  100
C                     DO   COUNT
C/EXEC SQL
C+ FETCH NEXT FROM C
C/END-EXEC
C/EXEC SQL
C+ UPDATE J4PP1 SET KEY2 = :INDEX WHERE CURRENT OF C
C/END-EXEC
C                     ENDDO
C                     TIME           TIME2   60
C/EXEC SQL
C+ CLOSE C
C/EXEC SQL

Again, my code could be garbage, but at least I knew enough to use a cursor.
Just for the fun of it, I tried a non-cursor approach:

C/EXEC SQL
C+ UPDATE J4PP1 SET KEY2 = :INDEX WHERE RRN(J4PP1) = :COUNT
C/END-EXEC

It took 12 seconds to update 10 records.  The moral of the story here is
that you have to use cursors for any type of transactional update.