Re: SQL versus READ for sequential processing

RPG is and probably always will be faster than SQL for Record Level
Access (RLA). RPG is optimized for RLA whereas SQL is optimized for
processing sets of records. At some point SQL might match RPG's speed
with a single row fetch, but I'd be willing to bet it will never be
faster.

Here's the motto I tell developers new to SQL, if you're using a
cursor you're probably doing something wrong.

If you want an SQL cursor to beat RPG, you have to do a multi-row
fetch of 100+ records or so at a time.

I'd be curious what IBM'er told you SQL FETCH was faster than RPG.

Some other benchmarks you may find interesting:
http://archive.midrange.com/rpg400-l/200809/msg00263.html

Charles Wilt


On Wed, Oct 8, 2008 at 9:57 AM, Åke Olsson <ake.olsson@xxxxxx> wrote:

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.