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.