The system will by default block records so you are probably getting a bump in performance on the reads from that.
Change your data structure for the fetch to a multiple occurrence DS or a DS array. Set the number of entries to what you would consider a reasonable blocking factor and try it again. I use (1028 * 128)/DS_length rounded down to determine my SQL DS sizes.
Rick
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Åke Olsson
Sent: Wednesday, October 08, 2008 8:58 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL versus READ for sequential processing
On a speech from an IBM'er at the conference I attended two days back he claimed that SQL FETCH was a much faster method for accessing data than the RPG READ.
Dramatic performance improvements were promised. I decided to do a test as follows:
I found a file with 62K records and created another file with same fields by "create table". Copied (mapped) the data to the new file.
I created two programs with same process (read all records do some minor processing on one of the fields, log on a printer file time program starts and ends).
Program A used regular read operations on the original file and program B declared a cursor on the new sql-created copy.
The results I got surprised me. The SQL version was significantly slower. "READ" comleted at .09 seconds and "FETCH" needed .541 seconds. Thus is 6 times slower!
There was no index involved, just plain sequential processing here.
Could I have missed out on any prerequisites for speeding up toe process.
This is the code for my test programs
1/ "READ" program
Fqsysprt o f 120 printer
Falcorl if e disk
D tstart s t
D tend s t
d stora s like(rlstor) dim(999)
D ix s 5 0
D tcount s 7 0
/free
tstart=%timestamp;
read alcorl;
dow not %eof;
tcount=tcount+1;
if %lookup(rlstor:stora)=*zero;
ix=%lookup(*blanks:stora);
stora(ix)=rlstor;
endif;
read alcorl;
enddo;
tend=%timestamp;
except rad;
*inlr=*on;
/end-free
Oqsysprt e rad 3
O tstart 26
O tend 60
O tcount 100
2/"FETCH" program
Fqsysprt o f 120 printer
D tstart s z
D tend s z
d stora s like(rlstor) dim(999)
D ix s 5 0
D tcount s 7 0
d dsalcorl e ds extname(calcorl)
/free
exec sql declare csr01 cursor for select * from calcorl;
exec sql open csr01;
tstart=%timestamp;
exec sql
fetch next from csr01 into :dsalcorl;
dow sqlstt = *zeros;
tcount=tcount+1;
if %lookup(rlstor:stora)=*zero;
ix=%lookup(*blanks:stora);
stora(ix)=rlstor;
endif;
exec sql
fetch next from csr01 into :dsalcorl;
enddo;
tend=%timestamp;
except rad;
*inlr=*on;
/end-free
c/exec sql
C+ CLOSE CSR01
C/END-EXEC
Oqsysprt e rad 3
O tstart 26
O tend 60
O tcount 100
Med vänlig hälsning / Best regards
Åke Olsson
Pdb DataSystem AB
Box 433 SE 551 16 Jönköping Sweden visit: Brunnsgatan 11
phone: +46 (0)36 342976 mobile: +46 (0)705 482976 fax: +46 (0)36 34 29 29
ake.olsson@xxxxxx <mailto:ake.olsson@xxxxxx> www.pdb.se
This e-mail and any attachments may contain confidential and privileged information. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient is unauthorized and may be illegal.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.
midrange.com
