× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.




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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.