AW: SQL versus READ for sequential processing

Hi,

Even if there are no access paths build over the physical file, the query
optimizer has to create or at least validate an access plan.
An access plan describes how the data must be accessed, that means a table
scan or index access is used, which indexes must be used and which temporary
objects must be created. After the access plan is created or validated, the
temporary objects get created and filled with data, that means the data path
get opened (the ODP gets created).

If the ODP is reusable and the activation group gets not closed after the
execution and CLOSQLCSR *ENDMOD was not specified in the compile command,
the ODP stays open after the second execution. That means if you want to
compare RPG and SQL, you have to do it after the second execution. For the
first and second execution native I/O is normally (much) faster.

As far as I have seen you compare native I/O with single row fetches. Even
after the third execution SQL may not be (much) faster, because row after
row gets read.
Instead of single row fetches try multiple row fetches. That means create a
Multi Occurrence Data structure (MOD) or an array data structure as big as
possible.
Then fetch as much as rows into this data structure in a single fetch and
read the data from this structure.
If not all rows could be read with the first fetch, continue with a second
and third multiple row fetch.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Åke Olsson
Gesendet: Wednesday, 08. October 2008 15:58
An: rpg400-l@xxxxxxxxxxxx
Betreff: 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.